[dba-SQLServer] TRIGGER question

Not sure about the # myseld but I'd like to add that 'inserted' and
'deleted' can in some instances contain multiple rows.
If you think this affects you you could do something like...
Select Count(*) from inserted
or
@@ROWCOUNT may work as well.

cheers

Michael M


Arthur -- I tried that, but it returned an error -- but I did not
include a # character -- I will try it that way. Thanks!

Susan H. 

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

_______________________________________________
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