Instead of trigger

[sparkbyte]

Can't seem to get this working.

Just want to copy the rows that would be deleted to another table then remove the record from the main table.

USE SQL_TEST
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Trigger [dbo].[trg_PSafety_Delete_Details] 
    On dbo.PSafety_LETTER_DETAILS

Instead of Delete

As
Begin

  Set Nocount On

	Select * 
	Into dbo.PK_PSafety_LETTER_DETAILS_DELETED
	From dbo.PSafety_LETTER_DETAILS As ld
          Inner Join Deleted As d
            On ld.ID = d.ID

	Delete ld
		From dbo.PSafety_LETTER_DETAILS ld
          Inner Join Deleted As d
            On ld.ID = d.ID

End

Thanks

John Fuhrman

 

[bborissov]

USE SQL_TEST
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Trigger [dbo].[trg_PSafety_Delete_Details]
    On dbo.PSafety_LETTER_DETAILS

Instead of Delete

As
Begin
  Set Nocount On

    Select * Into dbo.PK_PSafety_LETTER_DETAILS_DELETED
    FROM Deleted

    Delete ld
        From dbo.PSafety_LETTER_DETAILS ld
          Inner Join Deleted As d
            On ld.ID = d.ID
End

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[RiverGuy]

A couple of things about this trigger should be changed.

1. There is no reason to use an INSTEAD OF TRIGGER. Just use an AFTER/ default trigger.

2. SELECT INTO logic will not be repeatable as SELECT INTO creates a table. Perform a second delete, and you'll error out on trying to create a table which already exists. It would be better to create the table once manually, and then perform an INSERT INTO instead of a SELECT INTO.

 

[sparkbyte]

Would this be better.

USE [NSC_SQL]
GO
/****** Object:  Trigger [dbo].[CopyToDeleted]    Script Date: 04/15/2011 09:02:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[CopyToDeleted] 
   ON  [dbo].[PSafety_LETTER_DETAILS] 
   AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;
	
	Insert Into dbo.PSafety_LETTER_DETAILS_DELETED
		 Select *
		 From DELETED d
			Where Not Exists (Select * From PSafety_LETTER_DETAILS_DELETED);

END

Thanks

John Fuhrman