[dba-SQLServer] trigger help

Have I misunderstood inserted, as a temp table then? I thought SQL Server
created and used this table internally -- which  of course creates a problem
if you try to create a trigger before SQL Server creates the table. In my
case, I've already inserted records, so the table should exist, or at least.
Does SS create and destroy the table for each insert? Or does it exist for
the life of the db? 

You created a table using the name inserted with the appropriate schema,
created the trigger, and then deleted your table? 

Susan H.  


I think I have got round similar problems by creating the table as a normal
table, creating the trigger and saving it, then deleting the table.

HTH

David Emerson
Dalyn Software Ltd
New Zealand

At 15/01/2006, you wrote:
>I'm using Express Management Studio to create a trigger that uses the 
>temporary inserted table to update inventory -- simple example, but I 
>get an error that the schema in the target table doesn't match the trigger
schema.
>My guess is it doesn't like the use of inserted as a table name because 
>at the time I'm creating the trigger it doesn't exist. If that's the 
>case, I don't know how to get around that.
>
>Here's the trigger:
>
>CREATE TRIGGER UpdateInventory ON Sales.dbo.Orders AFTER INSERT AS 
>BEGIN
>     UPDATE Products
>     SET Products.InStock = (Products.InStock - Inserted.Qty)
>     FROM Products JOIN Inserted ON Products.ProdID - Inserted.ProdID 
>END GO
>
>I've checked the column names, just to be sure, and they above in 
>correct in that respect. I've run into trouble using the inserted 
>temporary table before, but I don't remember how I skirted it.
>
>Susan H.

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.17/226 - Release Date: 1/10/2006