July 21, 2008 at 5:01 am
Hi,
I would like to know how can I create a trigger which affet on
another table.
I have a table 'emp' as
empid avialable_leave
1 24
I have one another table 'empdet' as
empid leave
Now my question is if I insert a record in empdet as
empid leave
1 2
Then emp table will become as
empid avialable_leave
1 22
Please help to write a trigger for above query
Regards
Sunil Kumar
July 21, 2008 at 5:22 am
July 21, 2008 at 5:29 am
I have tried to write as follows but its working...
CREATE TRIGGER LEAVE_TRIGGER
ON EMP
FOR insert,UPDATE
AS
DECLARE @LEAVE INT
DECLARE @EMPID INT
BEGIN
insert into EMPDET values (@empid,@leave)
update emp set emp.leave = emp.leave-@leave
from inserted
where emp.empid=inserted.empid
END
July 21, 2008 at 5:52 am
su_kumar11 (7/21/2008)
I have tried to write as follows but its working...CREATE TRIGGER LEAVE_TRIGGER
ON EMP
FOR insert,UPDATE
AS
DECLARE @LEAVE INT
DECLARE @EMPID INT
BEGIN
insert into EMPDET values (@empid,@leave)
update emp set emp.leave = emp.leave-@leave
from inserted
where emp.empid=inserted.empid
END
Sunil,
I think you should first answer these questions
1. The trigger should work on which table?
2. What/When/How/Where are you going to pass the values for (@empid,@leave).
July 21, 2008 at 6:06 am
I am wrtting a trigger on emp table
and passing the value in empdet table
July 21, 2008 at 6:21 am
Please review the link the Michael Earl posted above and supply more information.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 21, 2008 at 6:27 am
Without some sample data and the DDL, this is hard to do, but the triggers need to be on the EMPDET table and I would split the INSERT and UPDATE triggers apart because the update is a bit more complicated.
I think this is close to what you want:
[font="Courier New"]CREATE TRIGGER EMPDET_INSERT
ON EMPDET
FOR INSERT
AS
BEGIN
UPDATE
E
SET
E.available_leave = E.available_leave - X.LeaveTaken
FROM
EMP E
INNER JOIN (SELECT empid, SUM(leave) AS LeaveTaken
FROM INSERTED
GROUP BY empID) X ON X.empid = E.empid
END
GO
CREATE TRIGGER EMPDET_UPDATE
ON EMPDET
FOR UPDATE
AS
BEGIN
UPDATE
E
SET
E.available_leave = E.available_leave - X.LeaveChange
FROM
EMP E
INNER JOIN (SELECT I.empid, SUM(I.empid) - SUM(D.empid) AS LeaveChange
FROM INSERTED I INNER JOIN DELETED D ON I.empid = D.empid
GROUP BY I.empid) X ON X.empid = E.empid
END[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply