Cyber-Drugs
asked on
Simple Trigger Question
Hi guys,
I was just wanted to find out...
In Oracle Database, you can create a AFTER COMMIT Trigger, so a trigger is fired AFTER a group of Insert Queries are made.
Does MS SQL have a similar feature?
Cheers!
I was just wanted to find out...
In Oracle Database, you can create a AFTER COMMIT Trigger, so a trigger is fired AFTER a group of Insert Queries are made.
Does MS SQL have a similar feature?
Cheers!
Not as far as I'm aware
Hope the following details will help you,
You can specify one of two options to control when a trigger fires:
AFTER triggers fire after the triggering action (INSERT, UPDATE, or DELETE) and after any constraints are processed. You can request AFTER triggers by specifying either the AFTER or FOR keywords. Because the FOR keyword has the same effect as AFTER, triggers with the FOR keyword are also classified as AFTER triggers.
INSTEAD OF triggers fire in place of the triggering action and before constraints are processed.
Each table or view can have one INSTEAD OF trigger for each triggering action (UPDATE, DELETE, and INSERT). A table can have several AFTER triggers for each triggering action.
Examples
A. Use the INSTEAD OF trigger to replace the standard triggering action
CREATE TRIGGER TableAInsertTrig ON TableA
INSTEAD OF INSERT
AS ...
B. Use the AFTER trigger to augment the standard triggering action
CREATE TRIGGER TableBDeleteTrig ON TableB
AFTER DELETE
AS ...
C. Use the FOR trigger to augment the standard triggering action
-- This statement uses the FOR keyword to generate an AFTER trigger.
CREATE TRIGGER TableCUpdateTrig ON TableC
FOR UPDATE
AS ...
You can specify one of two options to control when a trigger fires:
AFTER triggers fire after the triggering action (INSERT, UPDATE, or DELETE) and after any constraints are processed. You can request AFTER triggers by specifying either the AFTER or FOR keywords. Because the FOR keyword has the same effect as AFTER, triggers with the FOR keyword are also classified as AFTER triggers.
INSTEAD OF triggers fire in place of the triggering action and before constraints are processed.
Each table or view can have one INSTEAD OF trigger for each triggering action (UPDATE, DELETE, and INSERT). A table can have several AFTER triggers for each triggering action.
Examples
A. Use the INSTEAD OF trigger to replace the standard triggering action
CREATE TRIGGER TableAInsertTrig ON TableA
INSTEAD OF INSERT
AS ...
B. Use the AFTER trigger to augment the standard triggering action
CREATE TRIGGER TableBDeleteTrig ON TableB
AFTER DELETE
AS ...
C. Use the FOR trigger to augment the standard triggering action
-- This statement uses the FOR keyword to generate an AFTER trigger.
CREATE TRIGGER TableCUpdateTrig ON TableC
FOR UPDATE
AS ...
ASKER
Thanks for the replies,
Is there no third party code I can add to MS SQL to impliment an AFTER COMMIT trigger ability?
Cheers!
Is there no third party code I can add to MS SQL to impliment an AFTER COMMIT trigger ability?
Cheers!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>AFTER a group of Insert Queries are
Yes it does it you have to run the statement in a batch
and then you can specify the cursor inside the trigger on inserted system table to check each and every record
use standard trigger statement
CREATE TRIGGER TableCUpdateTrig ON TableC
FOR INSERT
AS ...
begin transaction
insert statemnt 1
insert statement 2
commit
go
Yes it does it you have to run the statement in a batch
and then you can specify the cursor inside the trigger on inserted system table to check each and every record
use standard trigger statement
CREATE TRIGGER TableCUpdateTrig ON TableC
FOR INSERT
AS ...
begin transaction
insert statemnt 1
insert statement 2
commit
go
>> use standard trigger statement
CREATE TRIGGER TableCUpdateTrig ON TableC
FOR INSERT
AS ...
begin transaction
insert statemnt 1 //1
insert statement 2 //2
commit //3
go
That will fire trigger at point 1 and 2, and not at 3.
CREATE TRIGGER TableCUpdateTrig ON TableC
FOR INSERT
AS ...
begin transaction
insert statemnt 1 //1
insert statement 2 //2
commit //3
go
That will fire trigger at point 1 and 2, and not at 3.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi guys,
Thanks for all the replies, I've ended up going for this solution:
AFTER INSERT Trigger which reads the first field of the record to see if it's the last record in the batch
-- If false, do nothing
-- If true, fire a stored procedure which was meant to be fired on the AFTER COMMIT
Not sure how to close this question, split the points between everyone or what? All answers were very good, but I ended up using in this instance the one I just mentioned...
Thanks for all the replies, I've ended up going for this solution:
AFTER INSERT Trigger which reads the first field of the record to see if it's the last record in the batch
-- If false, do nothing
-- If true, fire a stored procedure which was meant to be fired on the AFTER COMMIT
Not sure how to close this question, split the points between everyone or what? All answers were very good, but I ended up using in this instance the one I just mentioned...
>> AFTER INSERT Trigger which reads the first field of the record to see if it's the last record in the batch <<
That will work only if the rows are inserted one at a time. Remember that a trigger only fires once *per statement* in SQL Server, no matter how many rows are inserted (|updated|deleted) at one time.
That will work only if the rows are inserted one at a time. Remember that a trigger only fires once *per statement* in SQL Server, no matter how many rows are inserted (|updated|deleted) at one time.
>> Not sure how to close this question, split the points between everyone or what? All answers were very good, but I ended up using in this instance the one I just mentioned...
As a general rule, you award points for the 'correct' answer, but if other answers broadened your knowledge, or was worthwhile to you for whatever reason, you can award points for those as well.
So basically, feel free to split points, just leave a note as to what solution you used so people looking at the solution afterwards can see how to best solve the problem.
As a general rule, you award points for the 'correct' answer, but if other answers broadened your knowledge, or was worthwhile to you for whatever reason, you can award points for those as well.
So basically, feel free to split points, just leave a note as to what solution you used so people looking at the solution afterwards can see how to best solve the problem.
ASKER
ScottPletcher - It's C code, which does a loop making hundreds of Insert statements, so it should work fine.
riaancornelius - Well as you said, I have learned a few additional things in this question of mine, even though I didn't go for any of the listed solutions in the end.
I am going to split the points evenly, but for anyone who comes across this problem, look at my last comment to see what solution I chose to take.
Cheers guys!
riaancornelius - Well as you said, I have learned a few additional things in this question of mine, even though I didn't go for any of the listed solutions in the end.
I am going to split the points evenly, but for anyone who comes across this problem, look at my last comment to see what solution I chose to take.
Cheers guys!