[dba-SQLServer] TRIGGER question

In a trigger, there are two implicit tables hidden to all processes but the
trigger itself. The two tables are called #Inserted and #Deleted. In an
insert trigger the latter table is empty. In a delete trigger the former
table is empty. In an update trigger both tables contain one row (#deleted
contains the old data, #inserted the new data).

Thus, to determine the PK of the row being updated, and using your
column-name RecordID, you would do this:

SELECT RecordID FROM #Deleted

Which you could embed right in the column-list of your INSERT statement,
parenthesized of course.

HTH,
Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan
Harkins
Sent: July 9, 2005 6:25 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] TRIGGER question

I didn't ask the question right I'm sure. 

I'm not trying to create a new identity value for the audit record. I was
trying to pass the primary key value from the record being acted upon by the
DML statement to the audit table. 

But, that's not really appropriate in this trigger, as I've already figured
out. 

Thank you for trying to help. 

Susan H. 

It would go in place of where you have the variable:
IF UPDATE(royalty)
  INSERT INTO auditable (actiondescription, recordID, actionuser,
actiondate)
   VALUES ('U', @@IDENTITY, User_Name(), GetDate()) ELSE  INSERT INTO
auditable (actiondescription, recordID, actionuser,
actiondate)
   VALUES ('I',@@IDENTITY, User_Name(), GetDate())


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan
Harkins
Sent: Saturday, July 09, 2005 5:46 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] TRIGGER question


Well, if it works, I couldn't figure it out.  

Susan H. 

Have you tried @@IDENTITY ?



CREATE TRIGGER AuditRoyaltyUpdateInsert ON dbo.roysched FOR INSERT, UPDATE
AS DECLARE @precordID varchar(6)

IF UPDATE(royalty)
  INSERT INTO auditable (actiondescription, recordID, actionuser,
actiondate)
   VALUES ('U', @precordID, User_Name(), GetDate()) ELSE  INSERT INTO
auditable (actiondescription, recordID, actionuser,
actiondate)
   VALUES ('I', @precordID, User_Name(), GetDate())
 
=========The above trigger started  out as a simple audit trigger. Then, I
decided to add the table's primary key value to the audit record, and I
don't know how to do it -- how do I pass the current record's primary key
value, being dbo.roysched.title_id to the @precordID variable? 
 
Susan H. 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com