How to Create Trigger Insert with a SELECT LEFT OUTER JOIN

Question

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

Answer 1

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

• Sabin B: My pleasure ! .