[dba-SQLServer] TRIGGER question

Should have included the trigger: simple really -- very basic -- same for
the INSTEAD trigger that I'm trying to run to catch DML DELETE. 

CREATE TRIGGER [AuditRoyaltyUpdateInsert] ON dbo.roysched
  FOR INSERT, UPDATE
  AS
  IF UPDATE(royalty)
    INSERT INTO audittable (actiondescription, actionuser, actiondate)
     VALUES ('U', User_Name(), GetDate())
  ELSE
     INSERT INTO audittable (actiondescription, actionuser, actiondate)
      VALUES ('I', User_Name(), GetDate())
  SELECT Count(*) FROM #insert

I tried a PRINT, but couldn't get the syntax right -- I don't really care if
I can "see" the result of the count -- just thought if it didn't return an
error than I was at least talking to it -- know what I mean?

Thanks!
Susan H. 








 

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

I get the same error on #insert -- just can't seem to get this to work
Arthur. Is there perhaps some permission or something that I need to flag to
let me view data from these two tables? Or, perhaps it's a question of
timing? 

I have a simple insert/update trigger and at the end I put 

SELECT Count(*) from #insert

And it returned an invalid object error on #insert.

Susan H. 

Yes you're quite right, Michael. Should the update say "Update * FROM
Customers SET City = 'Los Angeles' WHERE City = 'New York'", then there
could be many rows in both #Inserted and #Deleted.

A.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Michael
Maddison
Sent: July 10, 2005 9:21 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [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


_______________________________________________
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