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:
>

Creating logfunctionality

Hello!

Does anyone have a link to some good information about creating log functionality for SQL-server. Basically I want to track changes made to a table, through an update-trigger. I would like to store both the old values and the new values (is this possible?), in a new table.

Thanks!

/MagnusI don't have a link for you but this is easy to do...

First readup on triggers in the Books on line. Checkout the following keywords:

"triggers, programming"
"triggers, multirow"
"triggers, inserted tables"
"triggers, last trigger"

In the trigger, you will want to select rows from the temporary memory-resident "inserted" or "deleted" tables. the "inserted" table holds the new stuff and the "deleted" table holds the old stuff.

For an insert you would select just from the "inserted" table and insert this into the new part of the audit table.

For an Update write a select to join the "deleted" table to the "inserted" table for the before and after stuff. Again, the "deleted" table is the old and the "inserted" table is the new. You will need to have well defined keys for this to work right.

For a delete, you guessed it, select data from the "deleted" table and insert the results into the old part of the audit table.

Remember that in SQL Server the triggers fire AFTER the Insert/Update/Delete. If you are using multipule triggers I would make this the last trigger to fire so you don't have to roll back your audit if something fails your business logic.

Post back if you have more questions.