Re: Killed by Triggers
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Wed, 06 Feb 2008 15:20:09 -0800
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
.
- Follow-Ups:
- Re: Killed by Triggers
- From: Will
- Re: Killed by Triggers
- Prev by Date: Re: SQL Server 2005--Access is Denied
- Next by Date: SQL Server 2005--Access is Denied
- Previous by thread: Re: Killed by Triggers
- Next by thread: Re: Killed by Triggers
- Index(es):
Relevant Pages
|
|
