February 17, 2011 at 12:31 pm
Hi, I never wrote a trigger before but I am having difficulties writting my first one.
My scenario is this: I have a hire status table which consist of employee id, hire date, termination date, and status. I bascailly want to add a trigger to this table so once a termination date has been entered, it will change the status column to Inactive. Any ideas?
Thanks
Sql Rookie
February 17, 2011 at 12:52 pm
Without more information it is a little difficult to answer your question but with some modifications to the code below you should be able to figure it out. The trigger below makes alot of assumptions (status column being a varchar column and not a BIT, termination_date column being a null column) Also your post doesn't consider what happens if an employee comes back? This trigger will not allow the inactive flag to come off until the termination date column has been cleared. It's all about the details! Good luck!
CREATE TRIGGER dbo.your_trigger_name
ON dbo.hire_status_table_name
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE HSTN
SET status_column_name = 'INACTIVE'
FROM dbo.hire_status_table_name HSTN
INNER JOIN inserted I
ON I.employee_id_column_name = HSTN.employee_id_column_name
WHERE I.termination_date_column_name IS NOT NULL
END
GO
February 17, 2011 at 12:59 pm
Sorry, I shouldve provided more details:
My Table Name is Hiredates.
Here are the fields:
staffid int,
hiredate datetime,
terminationdate datetime,
status varchar(10).
I was hoping to allow the user to put in a future termination date. Do you think I would be able write a comparison such as:
if terminationdate >= getdate()
begin
set @hirestatus ='A'
end
Thanks!!!!
February 17, 2011 at 1:35 pm
You can build plenty of logic into triggers but in my opinion its never a good place to define business logic. What you're attempting to do should really be handled in the application layer. Triggers are tucked away out of sight for many people and building logic into them typically causes maintainability issues.
If you have some compelling reason to build this logic into a trigger then you'll have an issue due to the fact that the trigger gets kicked off only when the row has been INSERTED or UPDATED. So take for example I set your employee record to have a termination date of two weeks from today. The trigger will catch that the termination_date is non_null and it will set your record to some pending_termination status but then your termination_date will come and go and then until someone triggers another event that UPDATES your employee record the trigger logic will never be evaluated again to update your record to terminated.
Hope this helps
-Mike
February 17, 2011 at 2:00 pm
I'm going to second the motion on the recommendation to not put this in a trigger.
You'll want to have the initial logic in a stored procedure, or whatever other code updates the termination date in the first place. That makes it easier to maintain and modify later, since all the action takes place in one set of code.
Then you'll probably need a scheduled job to run nightly (or some such) that will check for termination dates that are due, where the status isn't updated, and have the job update those. Depending on your system, you may also need login logic to check that an account is still active and that it doesn't have a termination date set that's prior to the login, in case someone logs in before the job runs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply