Auditing using Triggers

[autocutter]

I am having some problems with auditing insert/update/delete actions using triggers. What I am seeing is that I can catch the action & values from the insert/delete tables in to the audit table, but the original row changes it self to a previoues row until I do a requery of the original table. The trigger:

Create trigger auditInsert on dbo.Award
For Insert
as

Declare
@CustID as int,
@CustAmt as Varchar(30)

Set @CustID = Select CustID from Inserted
set @CustAmt = Select CustAmt from Inserted

set nocount on

Insert into AwardAudit(CustID, CustAmt, Date, User)
Values(@CustID, @CustAmt, GetDate(), sUser_sName())

set nocount off


What am I messing up-I have tried For, After, Instead of-used New & Old after the Inserted table reference, but nogo. And yes I have searched the

http://WWW for

the answer, but nogo II. Help!

 

[swampBoogie]

I really don't understand your talk about previous row. Anyhow, your code does not handle the case of multiple inserts.

Create trigger auditInsert on dbo.Award
For Insert
as
set nocount on

Insert into AwardAudit(CustID, CustAmt, Date, User)
select custid,custamt, GetDate(), sUser_sName()
from inserted

 

[autocutter]

"previous row" means that the original table "loses" the inserted row and visually replaces the values with values from the previous row. To view the actual inserted row I have issue a requery of the original table. Strange, but true. I;ll try your suggestion tho and see what happens

 

[autocutter]

swampBoogie-nix yours didn't work any better than mine did-does anyone have ANY suggestions??????????

 

[autocutter]

Okay, I found the answer the real hard way-Trial & Error!


The underlying 'Original' Table's identity field can not be the Primary Key elsewise the newly inserted record no longer meets the 'Original' Table's SQL critera' hence the requery finally shows the inserted record-Simple Huh! I'm lucky in that I have the customers' SSN for my unique value for the Primary Key

By the way, this 'rule' does not appear to apply to update or delete actions.