SQL trigger function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL trigger function

Author

Topic

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-09-08 : 13:08:42

I need to use SQL trigger function to check for update in my sales order table and if the column updated the record should be copied to an audit table.

For example my sales order table is as follows
order number
order date
order status

The trigger should check if order status changes to 9 if yes copy the record line to the audit table.

Any help appreciated.

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-08 : 15:13:39

You can use the UPDATE() function in the trigger to determine if the column was updated. Here's an example:

CREATE TRIGGER trig1
ON YourTable
AFTER UPDATE
AS

IF (UPDATE(OrderStatus))
BEGIN
INSERT INTO AuditTable (...)
SELECT ...
FROM inserted
WHERE OrderStatus = 9
END
GO

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-09-08 : 17:25:34

What would be the best way to schedule the above trigger?
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-08 : 19:14:33

You don't schedule a trigger. The trigger fires for the specified DML action. Each time an update happens to the table, it runs.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources