2

I work with SQL Server 2008 R2

I have a simple trigger

CREATE TRIGGER [dbo].[T_Personne_ITrig] ON [dbo].[Personne] FOR INSERT AS 
BEGIN
SET NOCOUNT ON 

insert into syn_HistoriquePersonne 
     (hpers_Timestamp, Supprime, ID, Nom, Prenom, Champ1, 
      Champ2, Champ3, Champ4 SiteAssocie)
 select  GETDATE(), 0, ID, Nom, Prenom, Champ1, Champ2, Champ3, 
       Champ4,  SiteAssocie
from inserted
END

It works properly. Problem is, I work on a program with an horrible code base so my boss don't want to trigger to ever cause a rollback on the table Personne even if it fails. I know it's really improbable, but he's scared of timeout in case of huge database activity... ANYWAY

So I searched about committing in triggers. And changed the trigger to : 

CREATE TRIGGER [dbo].[T_Personne_ITrig] ON [dbo].[Personne] FOR INSERT AS 
BEGIN
SET NOCOUNT ON 

COMMIT

insert into syn_HistoriquePersonne 
     (hpers_Timestamp, Supprime, ID, Nom, Prenom, Champ1, 
      Champ2, Champ3, Champ4 SiteAssocie)
select  GETDATE(), 0, ID, Nom, Prenom, Champ1, Champ2, Champ3, 
       Champ4,  SiteAssocie
from inserted
END

But the trigger kept shooting message

Transaction stopped in trigger, batch aborted.

So I made it like that : 

CREATE TRIGGER [dbo].[T_Personne_ITrig] ON [dbo].[Personne] FOR INSERT AS 
BEGIN
SET NOCOUNT ON 

COMMIT
BEGIN TRAN
insert into syn_HistoriquePersonne 
     (hpers_Timestamp, Supprime, ID, Nom, Prenom, Champ1, 
      Champ2, Champ3, Champ4, SiteAssocie)
 select  GETDATE(), 0, ID, Nom, Prenom, Champ1, Champ2, Champ3, 
       Champ4,  SiteAssocie
 from inserted
 END

It stopped doing the batch aborted, but it seems to never insert anything in my historic table... I read about the subject and this should work I think. But it doesn't...

Anyone else already had that problem and how can I fix that?

I am doing simple insert into to test my trigger.

Improve this question
6
  • 2
    Just don't even call ROLLBACK in your trigger - then you should be fine. Don't start new transactions in a trigger... the trigger should always be executed in the context of the operation that caused it to fire. Commented Feb 16, 2012 at 15:58
  • I won't call a rollback in my trigger, but the application will if a timeout occurs. I know that a begin tran is not optimum, but it does stop the error message. But the real question is why the insert is not working... Commented Feb 16, 2012 at 16:01
  • But you said under your first code snippet: it works properly - so what's the issue again?? If there's a timeout- there's nothing you can do inside the trigger to handle that anyway..... Commented Feb 16, 2012 at 16:02
  • I know it's probably a dumb requierement from my boss, but all that he want is that if any error happen in the trigger aka a time-out, he want the initial transaction on Personne to stay intact and not rolled back. It never happened yet, but he wants to be sure... Commented Feb 16, 2012 at 16:09
  • 1
    A trigger happens as part of the insert that fires the trigger. You can't write code inside the trigger to isolate it from an outer transaction. I'm sorry, but I'm not sure how to word this differently, you're barking up the wrong tree, it's not possible. If you really want the two pieces of code to be independant, remove the trigger, and place some "now update the history table" code in the sql called by your application. You can then control where and when you place the begins and commits. But you can't have a piece of code inside a transaction that's not part of that transaction. Commented Feb 16, 2012 at 16:29

1 Answer 1

Reset to default
3

Unfortunately you're barking up the wrong tree. You can't play with transactions in that way.

If the only concern was failed inserts, you could simply code a check around your insert. Or just be very throrough in ensuring the constraints on your table accurately reflect it's use. But, as you're also concerned about the duration of the process causing a command time-out, this won't cover you entirely (in fact it'll make that time-out very slightly more likely).

The only approach that I can see working is to massively simplify the insert statement, and insert something (all the data, or just the timestamp and id?) into a holding table which has no constraints, or indexes. You would then need a server side process that is called repeatedly to process your holding table.

As your case seems to be just maintaining a historic log, perhaps an option could be as simple as removing all constraints from the historique table. All the solutions are a bit dirty, but then the requirement from your boss seems a bit unusual; the answer should really be capacity planning in my opinion.

I don't know if that fits with your real world scenario, but I hope it helps.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

The historic table already have no constraint at all. But the problem is that even if I don't have the Begin Tran statement, if I put a commit in the trigger before I insert in my log table, the insert never seem to work... It's like I never called the insert at all and I don't understand why.
I tracked with some print if the code execute the insert and it is supposed to execute it. But for an unknown reason, nothing is inserted and I recieve no error message at all.
@AlexJean - As per the first line of my answer : You can not play with transactions like that. The BEGIN TRANSACTION and COMMIT TRANSACTION must be in the same scope as each other. If the App's SQL command begins the transaction, the App's command must commit it. As you hav eno constraints, etc on your table, you're not going to make it any faster, and that's the ONLY thing you could have done. (Trying to build command timeout fail-safes in the you are aiming for is simply not possible.)
@Alex Jean Why are you performing a COMMIT inside your trigger? Dems is right - remove it.
It was to make the initial transaction on the table Personne 'unrollbackable' in case the insert in the log table fails.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.