December 18, 2008 at 10:45 am
I am trying my first trigger,and think I understand it. But I am not sure how to put this all together...
I have an employee table where an employee can be Active, On Leave, or Termed. I want to set a trigger that fires when an employee is changed to Termed. Another field must also be checked because going from one company to another can initiate a T status, but if they were were Transferred Out - TRO, I do not want to do anything. I want this trigger to check the "Electronic Consent" table, and if the employee is in this table (they do not have to be) with a 'Y' (meaning that they have currently elected to receive their W-2 electronically), it should be changed to N. Termed employees cannot access our intranet and print their W-2's anymore.
So I have this:
CREATE TRIGGER dbo.Chg_Elec_Consent_for_Termed
ON Employee
AFTER UPDATE
AS
If EXISTS (SELECT * FROM inserted WHERE Employee.EmplStatus = 'T')
AND (SELECT * FROM EMPLOYEE
WHERE Employee.TERMReason <> 'TRO') --dont change employees that have been transferred
UPDATE CONSENT
SET CONSENT.ElectronicConsent = 'N'
WHERE CONSENT.EmpID = EMPLOYEE.EmpID
Am I close?
If not, could someone point me in the right direction?
Very much appreciated!!!!
December 18, 2008 at 10:55 am
Not quite.
I think you're looking for something more like this:
CREATE TRIGGER dbo.Chg_Elec_Consent_for_Termed
ON Employee
AFTER UPDATE
AS
UPDATE CONSENT
SET ElectronicConsent = 'N'
FROM inserted i
--INNER JOIN Employee E ON i.EmpID = E.EmpID -- Unique Identifier field join goes here
INNER JOIN CONSENT C ON i.EmpID = C.EmpID
WHERE i.TERMReason <> 'TRO' AND i.EmplStatus = 'T' --dont change employees that have been transferred
December 18, 2008 at 12:33 pm
Thank you Garadin.
I suppose I really dont have the conceptual piece down like I thought I did... I am creating a trigger on the entire table, and it fires if either of the fields cited in the trigger are updated?
If that is the case, what if one field is in the "inserted" table and the other is being written (ie the Term Reason field). Will the IF fail because the Term Reason is not yet updated?
I apologize, I promise I have been reading, but I guess I have to understand how this works inside SQL... I am having a brain block...
December 18, 2008 at 12:50 pm
laurav (12/18/2008)
Thank you Garadin.I suppose I really dont have the conceptual piece down like I thought I did... I am creating a trigger on the entire table, and it fires if either of the fields cited in the trigger are updated?
If that is the case, what if one field is in the "inserted" table and the other is being written (ie the Term Reason field). Will the IF fail because the Term Reason is not yet updated?
I apologize, I promise I have been reading, but I guess I have to understand how this works inside SQL... I am having a brain block...
You create a trigger on the table. The trigger fires once per operation. Note that this does not mean once per row, so your trigger must be able to function if someone updates multiple rows at once.
The theory behind my version is that it updates the Consent table (setting the Electronic Consent field to 'N') for any row in your inserted table (any row being inserted or updated) where the TERMReason <> 'TRO' AND the EmplStatus = 'T'.
This will always attempt the update to Consent, but due to the joins and conditionals, it will often update 0 rows. The reason it needs to be this way, is that if you had 25 rows updated, and 4 of them met your criteria, you need to be able to update 4 of them.
If you want to add in If criteria, the normal way to do it in triggers, is something like:
IF UPDATE(TermReason) OR UPDATE(EmplStatus)
BEGIN
UPDATE ...
END
December 18, 2008 at 3:08 pm
Thank you so much for the clarification - this makes sense, yay.
I very much appreciate your time! I owe you one :rolleyes:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply