[dba-SQLServer] trigger help

mwp.reid at qub.ac.uk mwp.reid at qub.ac.uk
Sat Jan 14 13:14:27 CST 2006


The table exists within the life time of the trigger. 

Try it using query analyser and see if it works. Its my birthday party now so if it dosnt work give me a shout and I will check in the morning.

U sure this isn’t  an sql express issue not a trigger issue?

-----Original Message-----
    From: "Susan Harkins"<harkinsss at bellsouth.net>
    Sent: 1/14/06 7:04:11 PM
    To: "dba-sqlserver at databaseadvisors.com"<dba-sqlserver at databaseadvisors.com>
    Subject: Re: [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 Da    

[Message truncated. Tap Edit->Mark for Download to get remaining portion.]




More information about the dba-SQLServer mailing list