CODEWITHSUNDEEP
sqlsql-server
vatspoo
vatspoo

Reputation: 401

whats happening in this trigger?

I was learning trigger in ms sql server and came across the following code on a website. I know that a trigger is made for insert, update and delete command but could not understand whats happening after that. The website also doesnt explain the script. Please explain me whats going on here.

select * from employee

GO

ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
      1     Jason      40420       1994-02-01 00:00:00.000 New York   W
      2     Robert     14420       1995-01-02 00:00:00.000 Vancouver  N
      3     Celia      24020       1996-12-03 00:00:00.000 Toronto    W
      4     Linda      40620       1997-11-04 00:00:00.000 New York   N
      5     David      80026       1998-10-05 00:00:00.000 Vancouver  W
      6     James      70060       1999-09-06 00:00:00.000 Toronto    N
      7     Alison     90620       2000-08-07 00:00:00.000 New York   W
      8     Chris      26020       2001-07-08 00:00:00.000 Vancouver  N
      9     Mary       60020       2002-06-09 00:00:00.000 Toronto    W

(9 rows affected)

-- Creating and Using Triggers

CREATE TRIGGER myTrigger
            ON employee
FOR UPDATE, INSERT, DELETE
AS
    SELECT * FROM inserted
    SELECT * FROM deleted

GO


SELECT 'Before INSERT'
INSERT Employee (ID, Name) VALUES (31, 'Rick')
GO

Before INSERT

(1 rows affected)

ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
         31 Rick              NULL                    NULL NULL       NULL

(1 rows affected)

ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------

(0 rows affected)

SELECT 'Before UPDATE'
UPDATE Employee
   SET Name = 'Rickie'
 WHERE ID = 3

GO

Before UPDATE

(1 rows affected)

ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          3 Rickie           24020 1996-12-03 00:00:00.000 Toronto    W

(1 rows affected)

ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W

(1 rows affected)

SELECT 'Before DELETE'
DELETE FROM Employee WHERE ID = 3
GO

Before DELETE

(1 rows affected)

ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------

(0 rows affected)

ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          3 Rickie           24020 1996-12-03 00:00:00.000 Toronto    W

(1 rows affected)

Upvotes: 1

Views: 116

Answers (3)

Tao
Tao

Reputation: 14006

The other answers have already mentioned that the insert table contains rows that are being inserted as part of the operation, and the deleted table contains rows that are being deleted. The less intuitive part, maybe, is that an UPDATE is exposed to the trigger with both an inserted resultset (for the updated rows as they are AFTER the update), and a deleted resultset (for the updated rows as they were BEFORE the update).

Upvotes: 0

Rich Parker
Rich Parker

Reputation: 172

inserted and deleted are tables that triggers use that contain the values being inserted and deleted (duh!) into your table.

The example is showing you that on insert the value appears in the inserted table, on delete in the deleted table and on update in both tables as an update is treated like a insert followed by a delete.

Upvotes: 1

matgerva
matgerva

Reputation: 72

Base concept of trigger is : EVENT->CONDITION->ACTION.

After an event (INSERT,UPDATE,DELETE) occurs the trigger evaluates a condition ( in your example there isn't a condition, so it's always true)

When you work with trigger you have access to two set of data:

-inserted (rows inserted during operation, that maybe insert,update or delete) -deleted (rows deleted during operation that maybe insert,update,delete)

When you do an INSERT inserted set contains rows inserted:

(1 rows affected)

ID name salary start_date city region

     31 Rick              NULL                    NULL NULL       NULL

deleted set doesn't contain nothing (you change nothing in insert):

ID name salary start_date city region

(0 rows affected)

Same logic for update :older rows are in deleted newer in inserted. For delete: inserted set is empty and deleted set is the row deleted

Upvotes: 2

Related Questions