June 11, 2002 at 8:37 am
I require to peform insert, update, delete operation on more than one table based on the values being updated, deleted in a table.
I have trigger on the table being update to do the necessary.
But this does not work if I do any operations using UPDATE or DELETE statements.
While going through BOL I came across "Multirow Considerations"
In this there a note
"The use of cursors in triggers is not recommended because of the potentially negative impact on performance. Use rowset-based logic rather than cursors to design a trigger that affects multiple rows."
Well, I am unclear about "rowset-based logic".
Is there any way other than using Cursor or doing a front-end coding?
IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!
SD
Regards,
Sachin Dedhia
June 11, 2002 at 9:13 am
I think what they are trying to say there is that you will impact performance if you use a cursor within your trigger to step through the inserted or deleted update rowset. The proper way to handle a multirow update or delete would be to join to the inserted or deleted logical tables.
Lets take this for example:
CREATE TABLE TEST1 (TEST1_PKEY INT PRIMARY KEY NOT NULL, DATA VARCHAR(64))
CREATE TABLE TEST2 (TEST1_KEY INT, DATA VARCHAR(64))
INSERT INTO TEST1 (TEST1_PKEY,DATA) VALUES (1,'TESTA')
INSERT INTO TEST1 (TEST1_PKEY,DATA) VALUES (2,'TESTB')
INSERT INTO TEST2 (TEST1_KEY,DATA) VALUES (1,'TEST1')
INSERT INTO TEST2 (TEST1_KEY,DATA) VALUES (1,'TEST2')
INSERT INTO TEST2 (TEST1_KEY,DATA) VALUES (2,'TEST3')
/* POORLY WRITTEN TRIGGER CODE
CREATE TRIGGER TEST2_TRIGGER ON TEST2
FOR UPDATE AS
IF UPDATE(DATA)
BEGIN
DECLARE @INS_DATA VARCHAR(64)
DECLARE @INS_PKEY INT
DECLARE C1 CURSOR FOR SELECT * FROM INSERTED
FETCH NEXT FROM C1 INTO @INS_PKEY, @INS_DATA
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE A SET DATA = B.DATA FROM TEST2 A JOIN TEST1 B ON A.TEST1_KEY=B.TEST1_PKEY WHERE A.TEST1_KEY=@INS_PKEY
END
FETCH NEXT FROM C1 INTO @INS_PKEY, @INS_DATA
END
*/
-- WELL WRITTEN TRIGGER TO HANDLE MULTIROW UPDATES EFFICIENTLY
CREATE TRIGGER TEST2_TRIGGER ON TEST2
FOR UPDATE AS
IF UPDATE(DATA)
BEGIN
UPDATE A SET DATA = B.DATA FROM TEST2 A
JOIN TEST1 B ON A.TEST1_KEY=B.TEST2_PKEY
JOIN INSERTED C ON C.TEST1_KEY=A.TEST1_KEY
END
Edited by - danw on 06/11/2002 09:14:07 AM
June 11, 2002 at 10:06 am
Hi Danw
Thank you for the prompt reply...
But, I am still unclear about "rowset-based logic"!!!
Well, I will get back tomorrow since its time to leave for me...
IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!
SD
Regards,
Sachin Dedhia
June 12, 2002 at 12:08 am
Well, here is my case
I have three tables
LeaveReq (ReqNo int, Empid int, Status varchar(3), ...)
Leaving (ReqNo int, LRReqNo int, Empid int, Statusvarchar(3), ...)
Rejoining (ReqNo int, LVReqNo int, LRReqNo int, Empid int, Status varchar(3), ...)
The value of Status can be one of REQ - Request, ACT - Active, AT - Action Taken, DEL - Deleted, CLD - Closed
Now when I update the Leaving.Status to REQ,
then corresponding LeaveReq records status should get update to AT only if the Status is ACT.
When I update the Leaving.Status to DEL
then, corresponding LeaveReq records status should get updated to REQ
only if the status is AT.
Also in this case 'ANY' previous Rejoining records status for that employee should get updated to ACT only if the status is AT
At the end of the month, all the records at status AT or ACT are update to CLD.
The major concern is the third case, since there is no link to it and this should happen only where the Leaving.Status is updated to DEL!!!
Also all the UPDATE statements will be called when I update the Status column. Will that not result in the overhead of the SQL Server?
The design has not been done by me.
IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!
SD
Regards,
Sachin Dedhia
June 12, 2002 at 9:36 am
This is how I would handle this... I am assuming that you can join on the empid column here... if not you should change the last statement to join on the the LVReqNo column. By the way, are there any foreign key constraints in place?
CREATE TRIGGER TR_LEAVING_UPDATE ON LEAVING
FOR UPDATE AS
IF UPDATE(STATUS)
BEGIN
UPDATE A SET STATUS = 'AT' FROM LEAVEREQ A JOIN LEAVING B ON B.LRREQNO=A.REQNO JOIN INSERTED C ON B.REQNO=C.REQNO WHERE C.STATUS = 'REQ' AND A.STATUS = 'ACT'
UPDATE A SET STATUS = 'REQ' FROM LEAVEREQ A JOIN LEAVING B ON B.LRREQNO=A.REQNO JOIN INSERTED C ON B.REQNO=C.REQNO WHERE C.STATUS = 'DEL' AND A.STATUS = 'AT'
UPDATE A SET STATUS = 'ACT' FROM REJOINING A JOIN LEAVING B ON B.EMPID=A.EMPID JOIN INSERTED C ON B.REQNO=C.REQNO WHERE C.STATUS = 'DEL' AND A.STATUS = 'AT'
END
Let me know if this makes sense and always test code before you implement it.
Thanks,
Dan
June 13, 2002 at 6:50 am
Well, no foreign key constraints in place...
I did not get time to work on this...will do it after weekend...
probably on sunday...
Thanks for the reminder... "always test code before you implement it"
Will get back to you soon...
IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!
SD
Regards,
Sachin Dedhia
June 17, 2002 at 10:53 am
Let me know how it goes...
June 17, 2002 at 10:56 pm
Was very busy...could not reply...
Well, it works fine, but I have to do similar operations when Rejoining Status is updated.
When I update Rejoining Status to ACT,
then corresponding Leaving Status should be set to AT only if the Leaving Status is ACT
When I update Rejoining Status to DEL,
then corresponding Leaving Status should be set to ACT only if the Leaving Status is AT
Now when I update Leaving, the Leaving UPDATE trigger gets fired, updating the Rejoining record(s), due to which Rejoining UPDATE trigger gets fired, updating the Leaving record(s)
The result is indirect recursion...I have set the nested triggers option to 0...but yet to test the cases...
Will let you know...
IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!
SD
Regards,
Sachin Dedhia
June 18, 2002 at 12:19 am
When RECURSIVE_TRIGGERS option for a database is set OFF, direct recursion is prevented. To diable indirect recursion the nested trigger server option has to be set to 0.
What about other databases residing on the same server that might require nested trigger option?
What if some other tables in our database reiquire indirect recursion?
There is also a way by which one can disable and enable triggers...but that does not sound to be a good option!
What have you to say about this?
IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!
SD
Regards,
Sachin Dedhia
June 18, 2002 at 9:46 am
If you are worried about disabling functionality on other parts of your server then you could handle this programatically on each trigger. This would be an example on how it could be handled on the Leaving table trigger:
if update(status) -- check status column update
begin
If (trigger_nestlevel(objectid('leaving')) = 0) -- only if this is not nested
begin
--do trigger work here
end
end -- end status column was updated
June 18, 2002 at 10:00 am
Thanks a lot for the solution...
Will get back to you in a day or two...busy with some more things...
IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!
SD
Regards,
Sachin Dedhia
June 30, 2002 at 9:20 am
Hi Dan
I am sorry, could not get back to you.
Truly speaking I haven't tested with the solution you have given and I cannot test it till 15th.
Will let you know about it.
Well, actually I wanted your view on the design that I have mentioned. I do not agree with the design, things could have been done using a single table...
The Leaving table stores the Actual leaving date while the Rejoining table stores the Actual Rejoining date.
One more thing to mention is that, if the Actual Leaving date or Rejoining date entered is wrong then a new record is added into the necessary table with the same Leave Request No.
Looking forward for you view point...
Excuse me for the delays as I am hooked to something else at this moment.
IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!
SD
Regards,
Sachin Dedhia
July 1, 2002 at 11:18 am
Unfortunately, I don't think I understand enough about your system to be able to make decisions about the database design. My own personal preference is to store similar data in a single table with an int status column, but I'm not sure if that would help out your cause.
If a new record is added because of a different date what should be done with the existing data for the same LeaveRequest number? What component of you application is added the extra record? Is this required behaviour?
July 7, 2002 at 7:46 am
> My own personal preference is to store similar data in a single table with an int status column, but I'm not sure if that would help out your cause.
I also do agree with you...
> If a new record is added because of a different date what should be done with the existing data for the same LeaveRequest number?
The only requirment for the existing data, to be more precise, the leave and rejoining dates is for further calculations based on the Number of Leave days...and for history which is a secondary issue.
IF U DON'T SEEK PERFECTION, U CAN NEVER REACH EXCELLENCE!!!
SD
Regards,
Sachin Dedhia
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply