Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Multiple after update triggers on same table Expand / Collapse
Author
Message
Posted Tuesday, March 18, 2008 1:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 4, 2008 1:12 AM
Points: 3, Visits: 6
I have a table on which simultaneous updates can happen at the same time. I have written an after update trigger to do some processing of data.

But when multiple updates are getting fired at the same time, the first trigger runs successfully, but the rest of the triggers are aborted.

Can any one help me on how to make multiple updates run simultaneously on the same table at the same time?

Thanks in advance
Post #470663
Posted Wednesday, March 19, 2008 3:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:45 AM
Points: 1,253, Visits: 919
If you have multiple update triggers, be sure they don't overlap (functionally).
IOW, if you have two and they both update own table, one will fire the other one. In such case merge them into one or at least the update part.
You didn't mention what goes wrong nor what the triggers do.

From Jeff's signature:
For better, quicker answers, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #471438
Posted Wednesday, March 19, 2008 3:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:03 AM
Points: 312, Visits: 1,127
what errors are you getting?
Post #471439
Posted Wednesday, March 19, 2008 5:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 4, 2008 1:12 AM
Points: 3, Visits: 6
Hi Robert,
There is only one after update trigger on a table that contains production order details. The Quantity Produced column in this table will be updated from various shop floor locations (HMI). Do multiple triggers can be fired. There is no trigger getting called within a trigger.

Within the trigger we first save the production order number in a temporary table and then call a java program using xp_cmdshell for the Production order being updated and then do the confirmation in SAP for all the orders in the temporary table.

Now when we update 2 different production orders from different systems, the triggers get fired... but the result says that only the first triggers caused the successful execution of calling a java program. The other one didnt executed...

Error Displayed:

[color=#FF0000]Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.[/color]

OR

Only the first trigger gets executed properly, the second doent execute


Following is the code that we have used in the trigger...

DECLARE
@orderNumber VARCHAR(14),
@cmd varchar(1000)
BEGIN
WAITFOR DELAY '00:00:01'

--Retrieve updated Quantity
SELECT @orderNumber=PARTSMANUFACTURING.M01_PRODUCTIONORDERNUMBER
FROM PARTSMANUFACTURING,INSERTED
WHERE PARTSMANUFACTURING.M01_PRODUCTIONORDERNUMBER=INSERTED.M01_PRODUCTIONORDERNUMBER;

--Insert Updated values in temporary table
BEGIN TRAN
INSERT INTO T_CONFIRMED_ORDERS VALUES(@orderNumber,GETDATE(),'SETConnector',GETDATE(),null,null,null);
COMMIT TRAN;

PRINT 'CALLING JAVA PROGRAM'
--call JAVA program
SET @cmd='java -cp C:\TCS\SETConnector\JavaTest;C:\TCS\SETConnector\SQLJDBC\sqljdbc.jar;C:\TCS\SETConnector\jco\jar\sapjco.jar; OrderConfirmation';
EXEC xp_cmdshell @cmd
END

Post #471507
Posted Wednesday, March 19, 2008 6:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:01 PM
Points: 35,372, Visits: 31,923
Start by removing the BEGIN TRAN/COMMIT statements. They serve no use here because you have no error detection.

Check your Java program... is it keeping a file open?

Get rid of the WAIT FOR... it makes a long transaction even longer.

Last but not least... if someone inserts 1000 rows and two of them have the same order number, BOOM! Code will fail because you try to put the order number into a variable which can only accept one row. This isn't like Oracle where you write a single row trigger and tell it FOR EACH ROW... all the rows you inserted are in a single INSERTED table. You will need to rewrite this trigger to be set-based instead of RBAR.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #471514
Posted Wednesday, March 19, 2008 6:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:03 AM
Points: 312, Visits: 1,127
Not sure what you gain from the explicit transaction.

Can you pass the data you insert to the table to the java app as parameters? That may release you from having put any locks on the table in the Java app...
Post #471518
Posted Wednesday, March 19, 2008 6:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:45 AM
Points: 1,253, Visits: 919
First of all, executing a java program from a trigger is a bad idea, because transactions must be short. Second, I'd guess the java program connects to database and performs some checks and updates, which is also a bad idea as it creates another transaction with its own locks. Third, java programs have its own problems, if not coded carefully.

It would be better if trigger performed all necessary checks.
Post #471526
Posted Wednesday, March 19, 2008 6:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:45 AM
Points: 1,253, Visits: 919
On second look it seems like everything is coded to slow down updates:
WAITFOR DELAY '00:00:01'

What is it for?

SELECT @orderNumber=PARTSMANUFACTURING.M01_PRODUCTIONORDERNUMBER
FROM PARTSMANUFACTURING,INSERTED
WHERE PARTSMANUFACTURING.M01_PRODUCTIONORDERNUMBER=INSERTED.M01_PRODUCTIONORDERNUMBER;

1. This works fine for single record update only.
2. why join to PARTSMANUFACTURING since all you fetch is M01_PRODUCTIONORDERNUMBER? It's the same as:
SELECT @orderNumber=M01_PRODUCTIONORDERNUMBER FROM INSERTED


After all this, trigger executes external java program without parameters, which presumably scans the whole temp table, so a single record update takes some seconds.

Post #471530
Posted Wednesday, March 19, 2008 12:57 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 9,294, Visits: 9,484
I am going to guess that the WAITFOR DELAY is only there to make testing this case easier (I have done this before) and is not normally there.

I am further going to guess that the Java programs are requerying the table in a way that is locking out the second INSERT or its trigger or its Java program. Somewhere in there the circuit close and a deadlock ensues.

I recommend what others here have said: 1) don't call a program from a trigger (use Service Broker to shunt this work off asynchronously if necessary) and 2) don't forget that SQL Server Triggers fire per-statement, NOT per-row as in other databases.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #471841
Posted Wednesday, March 19, 2008 10:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 4, 2008 1:12 AM
Points: 3, Visits: 6
Hi All,

Thanks a lot for all these prompt replies....

Scenario:
----------
There are around 30 HMIs accross 3 production plants that will fire update comfirmation on PartsManufacturing Table which is centrally located in SQL Server. It is likely that multiple updates may happen at a single point of time.
Since this confimation needs to happen in a realtime basis, we have to have an event based solution. Hence we are calling a trigger. If there is any other way out to make this scene in realtime you can even suggest that.

I can think on the following lines now... Correct me if I am wrong

1) I can use a cursor in the trigger to get all the records from the Inserted table and populate the temporary table.
(Assuming that Inserted table has only records from Partsmanufacturing Table, which means there are separate Inserted tables being created for different tables--correct me if i am wrong)

2) May be use the Service Broker to call java program and process the temporary table.

One thing I forgot to mention is that afrer the record has been processed from the temporary table I am deleting that record.

The fields of temporary table are Production Order Number & a Time Stamp. So even if there are multiple updates on the same production order they will be saved with different Time Stamp and a new entry is created.

I have tried to work with Service Broker but didnt succeed on that part, may be you ppl can guide me how to go about...

I have even tried to do some R&D on SQL Server Jobs, but again when the second trigger fires, the first Job is still running so the Job started by second trigger is aborted. The delay in the trigger was added keeping this in mind that If the previous trigger is still executing, the other will wait for some seconds and then go ahead with the execution.

I have also tried to call a Java program by passing Order Number as a comman line argument within a trigger.... but somehow the program hangs out and the query status still displays executing... The same program from command prompt with same argument works perfectly fine and the output comes within 5-6 seconds.

I am new to SQL Server 2005 so may be the code that I have written is bad perfomance wise.

Please guide me with this trigger... I am working on it madly but have not yet come to a solution...
Post #472056
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse