August 4, 2008 at 8:31 am
What is the difference between Instead and After Trigger in sql server ?
Give examples on Instead and After Trigger practically?
August 4, 2008 at 8:45 am
This is from BOL.
AFTER
Specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.
AFTER is the default when FOR is the only keyword specified.
AFTER triggers cannot be defined on views.
INSTEAD OF
Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. INSTEAD OF cannot be specified for DDL triggers.
At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, you can define views on views where each view has its own INSTEAD OF trigger.
INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.
This is an example for after trigger - sends an email message to specific person when a customer table has changed.
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the sales force.'
GO
August 4, 2008 at 8:52 am
AFTER triggers fire after changes are applied to the underlying table, INSTEAD OF triggers fire when you try to update the underlying table, but, instead of applying changes, only the trigger is fired.
Example: suppose you have a Customers table and you don't want negative customerId
CREATE TABLE Customers (
Id int,
Name varchar(50)
)
-- Turn negative id to positive: applies changes to the data already inserted
CREATE TRIGGER TR_Customer_After
ON Customers
AFTER INSERT
AS
BEGIN
UPDATE Customers
SET Id = (-1) * Id
FROM Customers A
INNER JOIN Inserted B
ON A.Id = B.Id
WHERE B.Id < 0
END
-- Throws exception for negative id
CREATE TRIGGER TR_Customer_Instead
ON Customers
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (
SELECT 1
FROM Inserted
WHERE Id < 0
)
RAISERROR('No negative ids allowed!!',10,1)
ELSE
INSERT INTO Customers SELECT * FROM Inserted
END
As you can see, if you use INSTEAD OF triggers, the trigger code will be responsible for copying the rows from inserted to the physical table.
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply