please explain the declaration of "i" and explain this code

Question

please explain the declaration of "i" and explain this code

sanalkumar889

Valued Member

Points: 67
More actions
August 4, 2011 at 1:37 am

#254923

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)
INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);
CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)
CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]
FOR INSERT
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';
insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER INSERT trigger fired.'
GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

"i" is a table alias for the INSERTED logical table, a special object available in triggers only. The INSERTED table is a logical representation of the rows modified by the current statement on the table on which the trigger is defined. It contains all the "new" values:

1) rows inserted by an INSERT statement

2) new values for the rows modified by an UPDATE statement

Looks like the trigger *should* audit changes to the Employee_Test table, but it is implemented in a way that assumes that just one row gets modified at a time. You'd better change that code to handle multiple rows insert/updates.

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]

FOR INSERT, UPDATE

AS

BEGIN

insert into Employee_Test_Audit

(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

SELECT I.Emp_id,I.Emp_name,I.Emp_sal,

CASE WHEN D.Emp_ID IS NULL THEN 'Inserted Record -- After Insert Trigger.'

ELSE 'Updated Record -- After Insert Trigger.'

END,getdate()

FROM INSERTED AS I

LEFT JOIN DELETED AS D

ON I.Emp_ID = D.Emp_ID

END

Hope this helps

Gianluca

-- Gianluca Sartori

Manish_ Ten Centuries Points: 1070 More actions · Answer 2

The 'i' is simply the alias of the INSERTED table and

INSERTED & DELETED are also referred as magic tables in SQL SERVER.

- Manish