views:

337

answers:

1

How do I alias the inserted and deleted virtual tables in a trigger in MSSQL 2005 so that I can use another set of inserted and deleted virtual tables from an OUTPUT clause later in the trigger?

I need to alias these tables in a trigger as per http://msdn.microsoft.com/en-us/library/ms177564%28SQL.90%29.aspx.

[Edit]

I should have been clearer, and provided an example I guess.

I want to do essentially the following :

CREATE TRIGGER [dbo].[someTrigger]
ON [dbo].[Table_1]
FOR INSERT
AS

CREATE TABLE #tmpdatatable (field1 int, field2 int)

INSERT dbo.Table_2
    OUTPUT inserted.ident, ins.objid INTO #tmpdatatable
SELECT 2*objid
FROM inserted as ins (NOLOCK) 

.. do some stuff to the tmpdatatable .. 

DROP TABLE #tmpdatatable
PRINT 'processed inserted data'
+1  A: 

You alias them just like any other table alias:

FROM inserted as ins

or

FROM deleted as del

then you use the alias for the column definition

SELECT ins.RowID, ins.Name
FROM inserted as ins
Jonathan Kehayias