0

I need determine each of this this 3 commands in trigger [UPDATE,DELETE,INSERT].For last 2 I do next:

    IF EXISTS (SELECT * FROM inserted)
      BEGIN 

      END
    ELSE IF EXISTS (SELECT * FROM deleted)
      BEGIN 

      END

How can I get updating rows? Thanks.

Anton Putov
  • 1,951
  • 8
  • 34
  • 62
  • I don't understand what you are trying to achieve... – Jocelyn Sep 04 '12 at 22:35
  • 1
    Not sure what you're asking. You create the trigger `FOR UPDATE` in the first place, so all rows in `inserted` are updated, with their old copies in `deleted`. – GSerg Sep 04 '12 at 22:36
  • I want first: determine UPDATE command , second:get access to rows that will update existing – Anton Putov Sep 04 '12 at 22:38

1 Answers1

3

Not exactly sure what you're trying to accomplish, but you can test if it's an UPDATE if both inserted (values after update) and deleted (values before update) exist. From the documentation:

The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

Thus, if inserted exists but not deleted, it's an INSERT; if deleted exists but not inserted, it's a DELETE; if they both exist, it's an UPDATE.

João Silva
  • 89,303
  • 29
  • 152
  • 158