Geek Answers Handbook

TSQL: Trigger on Commit

We have a back-office system that inserts account information into the SQL database through the MSDTC stored procedure, the application inserts the header, then the detailed information.

I installed the CLR trigger in the header table, which runs when a record is inserted.

The problem I am facing is that the trigger fires before COMMIT TRANSACTION , which means that the details information may not be populated, which is required in the process that runs. Also, if the system fires ROLLBACK TRANSACTION , the trigger is already running.

I understand that triggers cannot be assigned COMMIT , but it was interesting to see if anyone had any thoughts.

I stumbled upon the proposed oracle solution, where you create a Materlised View that updates ON COMMIT , and try to find the TSQL equivalent, which is an indexed view, but no luck in implementing it.

Summarizing:
Is it possible to run indexed views on COMMIT TRANSACTION ?

CLR Trigger Code

Sends a JMS message to the queue that will be processed by the Sonic ESB .

 [Microsoft.SqlServer.Server.SqlTrigger(Name = "InvoiceTrigger", Target = "Table", Event = "FOR INSERT")] public static void InvoiceTrigger() { //Declare Connection vairables string connectionURL, connectionDomain, connectionUser, connectionPassword, connectionQueue; //Constant connectionUser = "user"; connectionPassword = "pass"; connectionQueue = "Queue"; //Local Environment connectionURL = "tcp://IP:2506"; connectionDomain = "Domain1"; //Create connection sonic domain SonicSend send = new SonicSend(connectionURL, connectionDomain, connectionUser, connectionPassword, connectionQueue); //Send Test message to pipe SqlCommand command; SqlTriggerContext triggContext = SqlContext.TriggerContext; SqlPipe pipe = SqlContext.Pipe; SqlDataReader reader; switch (triggContext.TriggerAction) { case TriggerAction.Insert: // Retrieve the connection that the trigger is using using (SqlConnection connection = new SqlConnection(@"context connection=true")) { connection.Open(); command = new SqlCommand(@"SELECT LSH_LINKCODE, DOC_CODE, LSH_DOCNUM FROM INSERTED;", connection); reader = command.ExecuteReader(); if (reader.HasRows) { string xml; char cr = (char)13; int i = 0; while (reader.Read()) { xml = @"<Invoice action='insert'>"; xml += "<LinkCode>" + reader.GetString(0).ToString() + "</LinkCode>"; xml += "<DocumentCode>" + reader.GetString(1).ToString() + "</DocumentCode>"; xml += "<DocumentNumber>" + reader.GetString(2).ToString() + "</DocumentNumber>"; xml += @"</Invoice>"; i++; send.testJMSsend(xml); } } reader.Close(); } break; } }
+4
source share
2 answers

After helpful comments, which indicated that using commit in a trigger was not possible.

I examined the ESB process that was triggered by the trigger, the process now requests data with the transmitted information, this fixed my problem because BEGIN TRANSACTION creates a data lock that the request does not, t return results until COMMIT TRANSACTION , which means that the process will not continue until the request returns data, so the account data will always be filled.

In addition, if there was a ROLLBACK TRANSACTION , the process should have disabled the error as expected.

+1
source

See this article, you probably have to use context_info, and then trigger the trigger later on for these entries ...

Is there a way to disable SQL Server startup only for a specific runtime?

0
source

More articles:


All Articles