Re: Killed by Triggers

Thanks for the code correction. In this case only 1 record should be inserted
at a time, but I understand it is always good to plan ahead. Once I figure
out this strange behaviour I will incorporate your code changes.

I stated my error messages in a reply to Kalen. I hope they can help you to
understand my issue.

Thanks
--
-Will


"Erland Sommarskog" wrote:

Will (Will@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I have INSERT and UPDATE triggers on my SQL2005 tables which record some
audit trail information. I noticed that they didn't seem to be working
so I reviewed them. They looked okay. I resaved them and carried on.
They used to work.

Then I found that users could no longer write to these tables. When I
disabled the triggers, users could again write records.

Since you don't gives us any error messages or describe what is not
working, it's difficult to say what is wrong. Nevertheless, the trigger
you posted should reallly look like this:

ALTER TRIGGER [tr_TABLE01_INSERT]
ON [dbo].[TABLE01]
AFTER INSERT
AS
BEGIN
UPDATE TABLE01 SET CREATEDBY = CURRENT_USER,
CREATEDATE = GETDATE()
FROM TABLE01 t
JOIN inserted i ON t.IDFIELD = i.IDFIELD
END

Else you will not handle multi-row operations correctly.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.