Re: Killed by Triggers

Hi Will

What do you mean that users could not write to the tables? Do you get an
error? What does it say? If you don't get an error message, what are your
symptoms?

Please note that your trigger seems to assume that there will only be one
row inserted. You'll have problems if multiple rows are inserted in a single
operation.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com


"Will" <Will@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:100B4CDF-AD27-4E0B-AF30-DC40D4E55AB3@xxxxxxxxxxxxxxxx
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. A sample trigger
is:


ALTER TRIGGER [tr_TABLE01_INSERT]
ON [dbo].[TABLE01]
AFTER INSERT
AS
BEGIN
DECLARE @ID AS INTEGER
SET NOCOUNT ON;
SELECT @ID = IDFIELD FROM inserted
IF @ID IS NOT NULL
UPDATE TABLE01 SET CREATEDBY = CURRENT_USER,
CREATEDATE = GETDATE()
WHERE IDFIELD = @ID
END

What could have I done to cause this?


--
-Will


.