Sql-server – How to Create Trigger Insert with a SELECT LEFT OUTER JOIN

sql-server-2005trigger

I want this trigger to be fired after an insert is made with the text, this is what I have so far

ALTER TRIGGER [xxx]
   ON  [dbo].[Main] 
   AFTER INSERT
AS 
BEGIN

    SET NOCOUNT ON;

    INSERT INTO dtsx.dbo.main(refno,subject,recdate,dateind,officefrom,author,status)
    SELECT refno,subject,datereceived,'REC','TEST',author,StatDesc
    FROM dbo.Main LEFT OUTER JOIN
        dbo.DocStat ON dbo.Main.Status = dbo.DocStat.StatID

END

I just want the last record i created to be inserted but instead all the records in the source table (dbo.main) are inserted to the target table (dtsx.dbo.main). Where do place the INSERTED clause, if any? TIA

Best Answer

Every trigger have some internal tables , INSERTED and DELETED. In this case, we use only the INSERTED table, which hold the records inserted.

ALTER TRIGGER [xxx] ON [dbo].[Main] 
AFTER INSERT 
AS 
BEGIN

    SET NOCOUNT ON;

    INSERT INTO dtsx.dbo.main(refno,subject,recdate,dateind,officefrom,author,status)

    SELECT refno,subject,datereceived,'REC','TEST',author,StatDesc 
    FROM INSERTED AS I 
    LEFT OUTER JOIN dbo.DocStat AS DS
    ON I.Status = DS.StatID

END
Related Question