Whenever a trigger fires in response to the INSERT,DELETE,or UPDATE statement,two special tables are created.These are the insert and the delete tables.They are also referred to as the magic tables.These are the conceptual tables and are similar in structure to the table on which trigger is defined(the trigger table).
The inserted table contains a copy of all records that are inserted in the trigger table.
The deleted table contains all records that have been deleted from deleted from the trigger table.Whenever any updation takes place,the trigger uses both the inserted and deleted tables.
There are 2 Magic Tables in SQL server Inserted and Deleted.
These are mantained by SQL server for Internal processing whenever an update, insert of delete occur on a table. However, we can refere these tables in a Trigger.
Whenever an update table statement is fired SQL server mantains the original row before updation in a deleted table and New (updated )row in a Inserted Table.
Same is the case when an insert is fired Only Inserted table is populated with inserted Row.
and when Delete table statement is fired Deleted table is populated with the deleted row
EVENT: Insert
INSERTED –>Contains the inserted rows
DELETED—->Empty
EVENT:Delete
INSERTED—>Empty
DELETED –>Contains the rows to be deleted
EVENT: Update
INSERTED—>Contains the rows after update(new values)
DELETED –>Contains the rows before update(old values)
The tables “INSERTED” and “DELETED” are called magic tables of the
SQL Server. We can not see these tables in the data base. But we can access these
tables from the “TRIGGER”
When we insert the record into the table, the magic table “INSERTED” will be created
In that table the current inserted row will be available. We can access this
record in the “TRIGGER”.
Following code Explains the magic table “INSERTED”
CREATE TRIGGER LogMessage
ON EMP
FOR INSERT
AS
DECLARE @EMPNAME varchar(50)
SELECT @EMPNAME= (SELECT EMPNAME FROM INSERTED)
INSERT INTO LOGTABLE(UserId,Message) values (@EMPNAME,’Record Added’)
GO
When we delete the record from the table, the magic table “DELETED” will be created
In that table the current deleted row will be available. We can access this
record in the “TRIGGER”.
Following code Explain the magic table “DELETED”
CREATE TRIGGER LogMessage
ON EMP
FOR DELETE
AS
DECLARE @EMPNAME varchar(50)
SELECT @EMPNAME= (SELECT EMPNAME FROM DELETED)
INSERT INTO LOGTABLE(UserId,Message) values (@EMPNAME,’Record Removed’)
GO
The magic tables “INSERTED” and “DELETED” are main concept of the “TRIGGER”.
By using these tables we can do lot of useful functionalities. The above code is
used to update the “LOGTABLE”. Like wise we can maintain stock..
Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns will cause an error.
