SimonPrice33
asked on
SQL trigger after update of a table
Hi experts,
I am trying to write a SQL tigger however not having done one before all the examples I have found are a bit daunting and I don't quite fully understand them.
The scenario is that a table called workorders.workorders has a column that is updated called status.
What I am looking to dos when this status changes to complete or closed that the field closed date gets the system damage.
I would be very grateful if someone could give me an example and break it down for me please.
Thanks
Simon
I am trying to write a SQL tigger however not having done one before all the examples I have found are a bit daunting and I don't quite fully understand them.
The scenario is that a table called workorders.workorders has a column that is updated called status.
What I am looking to dos when this status changes to complete or closed that the field closed date gets the system damage.
I would be very grateful if someone could give me an example and break it down for me please.
Thanks
Simon
What do you mean by the term "system damage"? Can you elaborate?
ASKER
Sorry, that was auto correct on the iPad.
System date it was meant to say
System date it was meant to say
Well, a few ways you could do it.
1) You could have a SQL Agent job that updates the table. Query would be something like this.
2) You could do it with an update through the UI itself...
Or something like that.
3) You want to use a trigger. Which, is probably the best way. So, here ya go. I'm not going to steal this tutorial, cause it's perfect. Gives you what you want, I think.
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i
Let me know if you have any questions.
1) You could have a SQL Agent job that updates the table. Query would be something like this.
update <tablename> set statusdate = date() where statusdate is NULL and status = 'Closed'2) You could do it with an update through the UI itself...
update <tablename> set statusdate = date() where ticketid = <%=ticketid%>Or something like that.
3) You want to use a trigger. Which, is probably the best way. So, here ya go. I'm not going to steal this tutorial, cause it's perfect. Gives you what you want, I think.
http://www.sqlteam.com/article/an-introduction-to-triggers-part-i
Let me know if you have any questions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am having trouble getting the link on my laptop, but will read through it on the iPad and try it shortly
CREATE TRIGGER workorders__TRG_UPDATE
ON dbo.workorders
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE(status)
BEGIN
UPDATE wo
SET [closed date] = GETDATE()
FROM inserted i
INNER JOIN deleted d ON
d.key_col = i.key_col
INNER JOIN workorders wo ON
wo.key_col = i.key_col
WHERE
i.status IN ('closed', 'complete') AND
ISNULL(o.status, '.') <> i.status
END --IF
ON dbo.workorders
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE(status)
BEGIN
UPDATE wo
SET [closed date] = GETDATE()
FROM inserted i
INNER JOIN deleted d ON
d.key_col = i.key_col
INNER JOIN workorders wo ON
wo.key_col = i.key_col
WHERE
i.status IN ('closed', 'complete') AND
ISNULL(o.status, '.') <> i.status
END --IF
ASKER
perfect thank you
You do realize that trigger/UPDATE will always update every row, right?
Quite. It sort of defeats the point of a TRIGGER. I suspect they will notice something is amiss when they start seeing all the same dates for all the rows, regardless of when or even whether they were updated or not!