T-Sql Trigger’s

Posted by james on September 27, 2009 Leave a comment (0) Go to comments

A trigger is a special kind of stored procedure that responds to specific events.
Triggers are pieces of code that you attach to a particular table.
Otherwise we needed to invoke the Business logic in every event.
Trigger’s are classified into the following three types,
INSERT trigger’s
DELETE trigger’s
UPDATE trigger’s
Basic Syntax,
CREATE TRIGGER <trigger name>
ON <table or view name>
[WITH ENCRYPTION]
{{{FOR|AFTER} <[DELETE] [,] [INSERT] [,] [UPDATE]>) |INSTEAD OF}
[WITH APPEND]
[NOT FOR REPLICATION]
AS
<sql statements
…
…
…>
INSERT Trigger
The code for any trigger that you mark as being FOR INSERT will be executed anytime that someone
inserts a new row into your table. For each row that is inserted, SQL Server will create a copy of
that new row and insert it in a special table that exists only within the scope of your trigger.That table is called INSERTED.

The big thing to understand is that the INSERTED table only lives as long as your trigger does.
Think of it as not existing before your trigger starts or after your trigger completes.
<! [if !supportLineBreakNewLine] >
<! [endif] >

DELETE Trigger
This works much the same as an INSERT trigger does, save that the INSERTED table is not created. Instead,
a copy of each record that was deleted is inserted into another table called DELETED that,
like the INSERTED table, is limited in scope to just the life of your trigger.
UPDATE Trigger
More of the same, save for a twist.
The code in a trigger declared as being FOR UPDATE will be fired whenever an
existing record in your table is changed. The twist is that there’s no such
table as UPDATED. Instead, SQL Server treats each row as if the existing record had been
deleted, and a totally new record was inserted. As you can probably guess from that,
a trigger declared as FOR UPDATE contains not one but two special tables called
INSERTED and DELETED. The two tables have exactly the same number of rows, of course.
Examble’s:
CREATE TRIGGER OrderHasCustomer
ON Orders
FOR INSERT, UPDATE
AS
IF EXISTS
(
SELECT ‘True’
FROM Inserted i
LEFT JOIN Customers c
ON i.CustomerID = c.CustomerID
WHERE c.CustomerID IS NULL
)
BEGIN
RAISERROR(‘Order Must Have Valid CustomerID’,16,1)
ROLLBACK TRAN
END

CREATE TABLE test_trigger
(col1 int,
col2 char(6) )
GO
INSERT INTO test_trigger VALUES (1, ‘First’)
INSERT INTO test_trigger VALUES (2, ‘Second’)
INSERT INTO test_trigger VALUES (3, ‘Third’)
INSERT INTO test_trigger VALUES (4, ‘Fourth’)
INSERT INTO test_trigger VALUES (5, ‘Fifth’)
GO
CREATE TRIGGER delete_test
ON test_trigger AFTER DELETE
AS
PRINT ‘You just deleted a row!’

GO
Now let’s put the trigger to the test. What do you think will happen when the following statement is executed?
DELETE test_trigger WHERE col1 = 0
If you execute this statement, you’ll see the following message:
You just deleted a row! (0 row(s) affected)
Dropping Trigger’s:

DROP TRIGGER <trigger name>

ALTER Trigger:

ALTER TRIGGER OrderHasCustomer
ON Orders
FOR INSERT, UPDATE
AS
IF EXISTS
(
SELECT ‘True’
FROM Inserted i
LEFT JOIN Customers c
ON i.CustomerID = c.CustomerID
WHERE c.CustomerID IS NULL
)
BEGIN
RAISERROR(60000,16,1,’CustomerID’,'Orders’,'CustomerID’,'Customers’)
ROLLBACK TRAN
END
Instead-Of-Triggers
SQL Server 2000 allows you create a second kind of trigger, called an instead-of trigger. An instead-of trigger, rather than the data modification operation that fires the triggers, specifies the action to take. Instead-of triggers are different from after triggers in several ways:
• You can have only one instead-of trigger for each action (INSERT, UPDATE, and DELETE).

• You cannot combine instead-of triggers and foreign keys that have been defined with CASCADE on a table. For example, if Table2 has a FOREIGN KEY constraint that references Table1 and specifies CASCADE as the response to DELETE operations, you will get an error message if you try to create an instead-of trigger for DELETE on Table2. However, you can have instead-of triggers for INSERT or UPDATE. Similarly, if you already have an instead-of trigger on Table2, you cannot alter the table to add a foreign key constraint with the CASCADE action for the same data modification operation.
• Instead-of triggers can never be recursive, regardless of the setting of the recursive triggers database option. For example, if an instead-of trigger is executed for INSERT into Table1 and the trigger does an INSERT into Table1, the instead-of trigger is not processed. Instead, the INSERT is processed as if there were no instead-of trigger for INSERT, and any constraints and after triggers will take effect.
Instead-of triggers are intended to allow updates to views that are not normally updateable. For example, a view that is based on a join normally cannot have DELETE operations executed on it. However, you can write an instead-of DELETE trigger. The trigger has access to the rows of the view that would have been deleted had the view been a real table. The deleted rows are available in a worktable, which is accessed with the name deleted, just like for after triggers. Similarly, in an UPDATE or INSERT instead-of trigger, you can access the new rows in the inserted table.

Here’s a simple example that uses a Table1 and Table2 and builds a view on a join of these tables:
USE pubs
SET NOCOUNT ON
drop table Table1
CREATE TABLE Table1
(a int PRIMARY KEY,
b datetime default getdate(),
c varchar(10))

drop table Table2
CREATE TABLE Table2
(a int
,message varchar(100))
GO

/* Insert 4 rows into Table1 */
INSERT INTO Table1(a) VALUES (1)
INSERT INTO Table1(a) VALUES (2)
INSERT INTO Table1(a) VALUES (3)
INSERT INTO Table1(a) VALUES (4)

/* Insert 6 rows into Table2 */
INSERT INTO Table2 VALUES (1, ‘first row’)
INSERT INTO Table2 VALUES (1, ‘second row’)
INSERT INTO Table2 VALUES (2, ‘first row’)
INSERT INTO Table2 VALUES (2, ‘second row’)
INSERT INTO Table2 VALUES (2, ‘third row’)
INSERT INTO Table2 VALUES (3, ‘first row’)
GO

/* Create a view based on a join of the tables
and then an instead-of trigger on the view
*/

CREATE TRIGGER DEL_JOIN
ON join_view
INSTEAD OF DELETE

AS
DELETE Table1
WHERE a IN (SELECT a1 FROM deleted)
DELETE Table2
WHERE a IN (SELECT a2 FROM deleted)

In the following example, I’ll create a contacts list view in the
pubs database consisting of the name, city, state,
and country of all authors, stores, and publishers:

USE pubs
GO
CREATE VIEW contact_list
AS
SELECT ID = au_id, name = au_fname + ‘ ‘ + au_lname,
city, state, country = ‘USA’
FROM authors
UNION ALL
SELECT stor_id, stor_name, city, state, ‘USA’
FROM stores
UNION ALL
SELECT pub_id, pub_name, city, state, country
FROM publishers
CREATE TRIGGER Insert_Contact
ON contact_list
INSTEAD OF INSERT
AS
IF @@ROWCOUNT = 0 RETURN
IF (SELECT COUNT(*) FROM inserted) > 1 BEGIN
PRINT ‘Only one row at a time can be inserted’
RETURN
END
check for a hyphen in the fourth position in the ID
IF (SELECT substring(ID,4,1) FROM inserted) = ‘-’
Inserting an author
INSERT into authors(au_id, au_fname, au_lname, city, state)
SELECT id, rtrim(substring(name, 1, charindex(‘ ‘,name) – 1)),
rtrim(substring(name, charindex(‘ ‘,name) + 1,
datalength(name) – charindex(‘ ‘,name))), city, state
FROM inserted
ELSE
Check for two nines at the beginning of the ID
IF (SELECT ID FROM inserted) like ’99[0-9][0-9]‘
Inserting a publisher
INSERT INTO publishers (pub_id, pub_name, city, state, country)
SELECT * FROM inserted
ELSE
Inserting a store
INSERT INTO stores(stor_id, stor_name, city, state)
SELECT id, name, city, state from inserted
RETURN
You can write similar instead-of triggers for updates and deletes.