About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Tuesday, April 26, 2011

How to use ORDER BY when Defining a View?

If you use Order By clause while creating SQL View, SQL Server throw you such kind of error

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Sloution: Use Top with Percent keyword while creating view. For example-

Create View  
As (Select Top 100 Percent col1, col2, col3 from Table11 order by col1,col2,col3)

Post Reference: Vikram Aristocratic Elfin Share

Can We Insert Data into a View?

We can not insert data into view instead we can insert data into the underline table of view. SQL Server will
allow you to insert data into the underlying table through a view with a condition:

*The insert columns must be limited to columns of a single underlying table:- This means if a view is composed of 2 or more tables then insert should be made to column of one table at time.

For example

CREATE VIEW vTest AS
    SELECT      tb1.tab1_id, tb2.tab2_id, tb1.value tab1_value, tb2.value
    tab2_value
    FROM        table1 tb1
    INNER JOIN  table2 tb2
    Where tb1.tab1_id=tb2.tab1_id


This is a view "vTest" made from two table table1 and table2 inner joined on
tab1_id field

Now if you insert data from view to underlying table of table1 and Table2 you
need to do this in this way:

INSERT INTO vTest (tab1_id, tab1_value) VALUES (3, 30);
INSERT INTO vTest (tab2_id, tab1_id, tab2_value) VALUES (1,3, 30); 

Post Reference: Vikram Aristocratic Elfin Share

Monday, April 4, 2011

Get Ping with every DML statement on your table.....TRIGGERS

A trigger is a database object that is Bind to a table. In many aspects it is similar to a stored procedure and are often referred to as a "special kind of stored procedure."  

Modifications to the table are made using INSERT,UPDATE,OR DELETE statements.Triggers are used to enforce data integrity and business rules such as automatically updating summary data. It allows to perform cascading delete or update operations. If constraints exist on the trigger table,they are checked prior to the trigger execution. If constraints are violated statement will not be executed and trigger will not run.Triggers are associated with tables and they are automatic . Triggers are automatically invoked by SQL SERVER. Triggers prevent incorrect , unauthorized,or inconsistent changes to data.
 
 SQL Server  has many types of triggers:
  1. After Trigger
  2. Multiple After Triggers
  3. Instead Of Triggers
  4. Mixing Triggers Type
Triggers that run after an update, insert, or delete can be used in several ways:
  • Triggers can update, insert, or delete data in the same or other tables. This is useful to maintain relationships between data or to keep audit trail information.
  • Triggers can check data against values of data in the rest of the table or in other tables. This is useful when you cannot use RI constraints or check constraints because of references to data from other rows from this or other tables.
  • Triggers can use user-defined functions to activate non-database operations. This is useful, for example, for issuing alerts or updating information outside the database.
  • Note: An AFTER trigger can be created only on tables, not on views.
Exercising with After Triggers

1)Working with INSERT Triggers  

CREATE TRIGGER invoiceUpdate ON [Orders]
FOR INSERT
AS

Begin
UPDATE p SET p.instock=[p.instock – i.qty]
FROM products p JOIN inserted I ON p.prodid = i.prodid

End
 
You created an INSERT trigger that referenced the logical inserted table. Whenever you insert a new  record in the orders table now, the corresponding record in the products table will be updated to subtract the quantity of the order from the quantity on hand in the instack coloumn of the products table.


2)Working with DELETE Triggers
DELETE triggers are used for restricting the data that your users can remove from a database. For example

CREATE TRIGGER VikramDelete ON [Customers]
FOR DELETE
AS
IF (SELECT name FROM deleted) = ‘Vikram’
BEGIN
PRINT ‘Danger...Can not remove customers with the name vikram’
PRINT ‘Transaction has been canceled’
ROOLBACK
END


DELETE trigger used the logical deleted table to make certain that you were not trying to delete a customer with a great name “Vikram” – if you did try to delete such a customer, you would be met with Anaconda in the form of an error message (which was generated by the PRINT statement that you entered in the trigger code).



3)Working with UPDATE Triggers
UPDATE triggers are used to restrict UPDATE statement issued by your users, or to back your previous data.

CREATE TRIGGER CheckStock ON [Products]
FOR UPDATE
AS
IF (SELECT InStock FROM inserted) < 0
BEGIN
PRINT ‘Cannot oversell Products’
PRINT ‘Transaction has been cancelled’
ROLLBACK
END
You created an UPDATE trigger that references the inserted table to verify that you are not trying to insert a value that is less than zero. You need to check only the inserted table because SQL Server performs any necessary mathematical functions before inserting your data.

 
Since I am running out of time, in next post i will try to explain INSTEAD OF TRIGGER.


Post Reference: Vikram Aristocratic Elfin Share 
BlueTangledKindle Wireless Reading Device, Wi-Fi, Graphite, 6" Display with New E Ink Pearl Technology