asked
delete copy
I have a very simple stored procedure ALTER PROCEDURE [dbo].[pMBSolutionsNarrativeDelete] @pk_Narrative_ID int AS BEGIN DELETE [dbo].[Narrative] WHERE [Narrative_ID] = @pk_Narrative_ID END I created a trigger named delcopy ALTER TRIGGER [dbo].[delnTrigger] ON [dbo].[Narrative] FOR DELETE AS INSERT INTO NarrativeDelete (Narrative_ID, MonthYear, Location, SubContractor, SiteManager, Date, MonitorSummary, MIM, MIF, MAM, MAF, Project_ID, BidPackage_ID) SELECT Narrative_ID, MonthYear, Location, SubContractor, SiteManager, Date, MonitorSummary, MIM, MIF, MAM, MAF, Project_ID, BidPackage_ID FROM Narrative when I execute pMBSolutionsNarrativeDelete @pk_Narrative(record 1) it delete. doesn't copy(what and I doing wrong?)
You are referencing the unbounded main table, and you need to be referencing the `deleted` virtual table. I.e. Instead of INSERT INTO NarrativeDelete (Narrative_ID, MonthYear, Location, SubContractor, SiteManager, Date, MonitorSummary, MIM, MIF, MAM, MAF, Project_ID, BidPackage_ID) SELECT Narrative_ID, MonthYear, Location, SubContractor, SiteManager, Date, MonitorSummary, MIM, MIF, MAM, MAF, Project_ID, BidPackage_ID FROM Narrative You want INSERT INTO NarrativeDelete (Narrative_ID, MonthYear, Location, SubContractor, SiteManager, Date, MonitorSummary, MIM, MIF, MAM, MAF, Project_ID, BidPackage_ID) SELECT Narrative_ID, MonthYear, Location, SubContractor, SiteManager, Date, MonitorSummary, MIM, MIF, MAM, MAF, Project_ID, BidPackage_ID FROM deleted
If my assumption is correct then, you are trying to insert the deleted records of Narrative table to NarrativeDelete table (Just like Auditing). In that event you should use Deleted Table inside your trigger. its simple replace your Insert query with INSERT INTO NarrativeDelete (Narrative_ID, MonthYear, Location, SubContractor, SiteManager, Date, MonitorSummary, MIM, MIF, MAM, MAF, Project_ID, BidPackage_ID) SELECT Narrative_ID, MonthYear, Location, SubContractor, SiteManager, Date, MonitorSummary, MIM, MIF, MAM, MAF, Project_ID, BidPackage_ID FROM Deleted
You need to change the select statement in the trigger to select that data from the pseudo-table deleted: ALTER TRIGGER [dbo].[delnTrigger] ON [dbo].[Narrative] FOR DELETE AS INSERT INTO NarrativeDelete (Narrative_ID, MonthYear, Location, SubContractor, SiteManager, Date, MonitorSummary, MIM, MIF, MAM, MAF, Project_ID, BidPackage_ID) SELECT Narrative_ID, MonthYear, Location, SubContractor, SiteManager, Date, MonitorSummary, MIM, MIF, MAM, MAF, Project_ID, BidPackage_ID FROM deleted /* <-- here is the change */ The select you have up to now is trying to get the information that you have already deleted. The pseudo-table stores the information that runs from the trigger. There are the pseudo-tables `inserted` and `deleted` which can be accessed through `INSERT`, `UPDATE` and `DELETE` triggers.
