CODEWITHSUNDEEP
sqlsql-servertriggers
Just a learner
Just a learner

Reputation: 28602

Can "after trigger" be replaced completely by "instead of trigger"?

I know basic trigger concepts and have played with it when I learn it. I don't have much practical experience on it, since I seldom use it. Now my question is, Can all after trigger be replaced completely by instead of trigger?

To me instead of trigger is kind of a "before trigger" and it fires before the action happens. So we can do something useful, or roll the transaction back. Since we can do anything, we can INSERT, UPDATE, DELETE. To me it seems like I can use instead of trigger to completely replace after trigger. Is that true?

Below is an example against SQL Server. The purpose of those two triggers are to prevent deletes. It seems both of them works. 

USE test;
GO
IF OBJECT_ID(N'dbo.a', N'U') IS NOT NULL
    DROP TABLE dbo.a;
GO

CREATE TABLE dbo.a (id INT, c1 INT);
GO

INSERT  INTO a
VALUES  (1, 1);
GO

-- instead of trigger
IF OBJECT_ID('dbo.t1', 'TR') IS NOT NULL
    DROP TRIGGER dbo.t1;
GO
CREATE TRIGGER dbo.t1 ON dbo.a
    INSTEAD OF DELETE
AS
BEGIN
    ROLLBACK;
    PRINT 't1: Not allowed to delete data  from dbo.a';
END
GO


-- delete a row from the table to test the trigger
DELETE  FROM a
WHERE   id = 1;
-- and it works!

-- drop the first trigger
IF OBJECT_ID('dbo.t1', 'TR') IS NOT NULL
    DROP TRIGGER dbo.t1;
GO

-- create an after trigger
IF OBJECT_ID('dbo.t2', 'TR') IS NOT NULL
    DROP TRIGGER dbo.t2;
GO
CREATE TRIGGER dbo.t2 ON dbo.a
    AFTER DELETE
AS
BEGIN
    ROLLBACK;
    PRINT 't2: Not allowed to delete data  from dbo.a';
END
GO


-- delete a row from the table to test the trigger
DELETE  FROM a
WHERE   id = 1;
-- and it works!


-- drop the second trigger
IF OBJECT_ID('dbo.t2', 'TR') IS NOT NULL
    DROP TRIGGER dbo.t2;
GO

Upvotes: 0

Views: 276

Answers (1)

benjamin moskovits
benjamin moskovits

Reputation: 5458

Yes they can be made equivalent except you have to remember that in an after trigger the action has taken place - its up to you to undo it typically through a rollback.

In an instead of trigger the action has not taken place and its up to you (in the trigger) to do the action. An after trigger is often done as an audit step. An instead of is usually done to prevent something from happening.

The deleted and inserted tables are populated in either case.

You can step through the following code to verify what I just said:

drop table dbo.t1

create table dbo.t1 (i1 int)

insert into dbo.t1 values (1),(5), (6)

select * from dbo.t1

; go

create trigger dbo.t2trig on dbo.t1
after delete AS

begin

select * from deleted

print 'in after trigger dbo.t1trig'

end

delete from dbo.t1 where i1 < 6

select * from dbo.t1

insert into dbo.t1 values (1),(5)

select * from dbo.t1

; go

drop trigger dbo.t2trig

; go

create trigger dbo.t2trig on dbo.t1
instead of delete AS

begin

select * from deleted

print 'in trigger instead of dbo.t1trig'

end

insert into dbo.t1 values (1),(5)

delete from dbo.t1 where i1 < 6

select * from dbo.t1

Upvotes: 1

Related Questions