difference between trigger after,for and in in sql

Hi

1. 'AFTER' Trigger should be fired once the all operations including constraints successfullt completed.
2. 'AFTER' Trigger is the default when 'FOR' is the only keyword specified.
3. 'AFTER' triggers cannot be specified on VIEWS

Here, I have created two triggers (Trg1, Trg2) on Table1. 'Trg1' Trigger used with 'AFTER' and 'Trg2' Trigger used with 'FOR'

CREATE TRIGGER TRG1 ON table1 AFTER INSERT AS

BEGIN

	RAISERROR('This is After Trigger...!',10,1)

END

GO



CREATE TRIGGER TRG2 ON table1 FOR INSERT AS

BEGIN

	RAISERROR('This also After Trigger...!',10,1)

END

GO

But, The both trigger created in same configuration...., Execute the script given below to identify the both triggers...

SP_HELPTRIGGER 'table1'

The result would be same... (Both are 'AFTER' trigger...)

trigger_name	isupdate	isdelete	isinsert	isafter	isinsteadof

TRG1	            0	            0	            1	            1	       0

TRG2	            0	            0	            1	            1	       0

Conclution: So, we can not differentiate the 'AFTER' and 'FOR' (FOR just like keyword...)

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

thanks.
please explain also cursors with examples.........

Thanks & Regards
Ramesh.B

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

Below link explains the different types of triggers
http://www.sql-server-performance.com/articles/dev/triggers_2000_p1.aspx

Thanks
Karthik
www.f5Debug.net

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

for cursors refer to this link
http://msdn.microsoft.com/en-us/library/ms172375.aspx

Thanks
Karthik
www.f5Debug.net

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.


CREATE TRIGGER trgTable on dbo.Table FOR INSERT,UPDATE,DELETE
CREATE TRIGGER trgTable on dbo.Table AFTER INSERT,UPDATE,DELETE

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Nice explanation Pandian Sir

Thanks & Regards
Hari

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

HIi Gopal,
Please Mark as Answer if it helpful to you..That helps others who search the same...

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator