SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple after update triggers on same table


Multiple after update triggers on same table

Author
Message
hitesh.p
hitesh.p
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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
Robert-378556
Robert-378556
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1758 Visits: 1010
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/
ianT
ianT
SSC-Addicted
SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)

Group: General Forum Members
Points: 416 Visits: 1299
what errors are you getting?
hitesh.p
hitesh.p
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84327 Visits: 41061
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ianT
ianT
SSC-Addicted
SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)

Group: General Forum Members
Points: 416 Visits: 1299
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...
Robert-378556
Robert-378556
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1758 Visits: 1010
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. Cool
Robert-378556
Robert-378556
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1758 Visits: 1010
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.
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14562 Visits: 9518
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."
hitesh.p
hitesh.p
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search