Creating SQL Triggers

Helllo,
I've done these in the past but it's been a while.
I'd like to create a trigger on a SQL table to track any time a record
in this tables gets updated with a zero value in one of the table
columns.
Client is having an issue where an estimated cost field is getting set
to zero by a certain process and I need a tool to track down the
culprit.
I'd like to trap for this column being set to zero on any update or
insert into this table and then dump this audit trail (user ID, spid,
time, date, etc..) out to a manually created sql table.
Any advice or articles that could help would be much appreciated.
Thanks,
JonCREATE TRIGGER NoZerosPlease ON [YourTable]
FOR UPDATE
AS
BEGIN
INSERT INTO [AuditTable]
SELECT SYSTEM_USER, @.@.SPID, APP_NAME(),
ISNULL(OBJECT_NAME(@.@.PROCID),'Not an sp'),
GETDATE(), d.[YourColumn] AS "PreviousValue"
FROM inserted i INNER JOIN deleted d on i.[PKColumn] = d.[PKColumn]
--more on conditions if you have a composite primary key
WHERE i.[YourColumn] = 0
END
You'll need to create the [AuditTable] table.
--
"JMo" wrote:

> Helllo,
> I've done these in the past but it's been a while.
> I'd like to create a trigger on a SQL table to track any time a record
> in this tables gets updated with a zero value in one of the table
> columns.
> Client is having an issue where an estimated cost field is getting set
> to zero by a certain process and I need a tool to track down the
> culprit.
> I'd like to trap for this column being set to zero on any update or
> insert into this table and then dump this audit trail (user ID, spid,
> time, date, etc..) out to a manually created sql table.
> Any advice or articles that could help would be much appreciated.
> Thanks,
> Jon
>|||Thank You Mark!!
Okay, I plan to create a table with a counter as a primary key and then
create a table with string fields to catch user, spid, and app name and
a date field the getdate function.
Am I on the right track?
Thanks again,
Jon
Mark Williams wrote:
> CREATE TRIGGER NoZerosPlease ON [YourTable]
> FOR UPDATE
> AS
> BEGIN
> INSERT INTO [AuditTable]
> SELECT SYSTEM_USER, @.@.SPID, APP_NAME(),
> ISNULL(OBJECT_NAME(@.@.PROCID),'Not an sp'),
> GETDATE(), d.[YourColumn] AS "PreviousValue"
> FROM inserted i INNER JOIN deleted d on i.[PKColumn] = d.[PKColumn]
> --more on conditions if you have a composite primary key
> WHERE i.[YourColumn] = 0
> END
> You'll need to create the [AuditTable] table.
> --
> "JMo" wrote:
>