difference between trigger after,for and in in sql

Posted by Gopal_nivas under Sql Server on 12/24/2010 | Points: 10 | Views : 6391 | Status : [Member] | Replies : 7
hi..

wat is the difference between for,after,in in triggers..

in wat scenario we are using tat..

give me some examples for tat...

need ur suggestions..
regards
gopal.s




Responses

Posted by: PandianS on: 12/24/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Bathala.ramesh430 on: 12/25/2010 [Member] Starter | Points: 25

Up
0
Down
thanks.
please explain also cursors with examples.........

Thanks & Regards
Ramesh.B

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

Posted by: Karthikanbarasan on: 1/6/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Karthikanbarasan on: 1/6/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
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

Posted by: Muhsinathk on: 9/13/2012 [Member] Bronze | Points: 25

Up
0
Down
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

Posted by: Hariinakoti on: 9/13/2012 [Member] Starter | Points: 25

Up
0
Down
Nice explanation Pandian Sir

Thanks & Regards
Hari

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

Posted by: Muhsinathk on: 9/14/2012 [Member] Bronze | Points: 25

Up
0
Down
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

Login to post response