Solved

Simple Trigger Question

Posted on 2006-07-10
12
2,320 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:Cyber-Drugs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 9

Expert Comment

by:riaancornelius
ID: 17071743
Not as far as I'm aware
0
 
LVL 3

Expert Comment

by:r_a_j_e_s_h
ID: 17071812
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 ...

0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17071903
Thanks for the replies,

Is there no third party code I can add to MS SQL to impliment an AFTER COMMIT trigger ability?

Cheers!
0
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 
LVL 9

Assisted Solution

by:riaancornelius
riaancornelius earned 167 total points
ID: 17071985
not that I know of, but you can maybe create a seperate table into which you insert a value after the commit, and put a trigger on that table. Dunno how you can check whether a commit happened though.
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17072025
>>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
0
 
LVL 9

Expert Comment

by:riaancornelius
ID: 17072066
>> 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.
0
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 167 total points
ID: 17072191
Sorry for previous post rianncornelius is right

It is less common for INSERT triggers, because the basic INSERT statement adds only a single row. However, because an INSERT trigger can be fired by an INSERT INTO (table_name) SELECT statement,

so what you have to do in that case is to insert all the rows in a temp table or table variable and then fire

INSERT INTO (table_name) SELECT from temp table to follow that approch and use the cursor inside the trigger on inserted table to fetch each record.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 166 total points
ID: 17074453
>> Does MS SQL have a[n] [AFTER COMMIT INSERT trigger]? <<

NO.  The trigger is *always* part of the transaction that the initial INSERT (| UPDATE | DELETE) is in.  If the trigger has a serious failure, the INSERT(s) will be rolled back automatically.

Probably the closest you could get to simulating an "AFTER COMMIT" trigger is to have the trigger start a job, with the job having a time-delay (WAITFOR), hopefully providing enough delay for the COMMIT to have taken place before the rest of the code in the job runs.
0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17074887
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...
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 17075024
>> 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.
0
 
LVL 9

Expert Comment

by:riaancornelius
ID: 17078924
>> 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.
0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17079632
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!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question