[dba-SQLServer] trigger help

I had a situation where I needed to create a table using fieldnames 
based on other data, then run a query based on the new 
table.  However, because the table wasn't permanent I couldn't create 
the query.  What I did was create a normal table (tblTableName), base 
the query on that (SELECT * FROM tblTableName).  Once the query was 
saved I could delete the table.  Since the query was only run when 
the temporary table was created it worked.  Mind you, the tables I 
created were not internal temp ones as I needed to access them 
outside of the sproc that created them.  The sproc deleted the 
previous copy of the table before creating the new one based on new data.

David Emerson
Dalyn Software Ltd
New Zealand

At 15/01/2006, you wrote:
>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
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com