Properties

Owner:	HumanResources
Table/View:	HumanResources.Employee
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Encrypted:
Description:	INSTEAD OF DELETE trigger which keeps Employees from being deleted.

Creation options

QUOTED_IDENTIFIER:
ANSI_NULLS:

Type

Instead of:
Insert:
Update:
Delete:

Objects that [HumanResources].[dEmployee] depends on

	Object Name	Owner	Object Type	Dep Level
	Flag	dbo	User Defined type	1
	Name	dbo	User Defined type	1
	NameStyle	dbo	User Defined type	1
	Phone	dbo	User Defined type	1
	Contact	Person	Table	2
	Employee	HumanResources	Table	3

Total: 6 object(s)

SQL

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee]
INSTEAD OF DELETE NOT FOR REPLICATION AS
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0
        RETURN;

    SET NOCOUNT ON;

    BEGIN
        RAISERROR
            (N'Employees cannot be deleted. They can only be marked as not current.', -- Message
            10, -- Severity.
            1); -- State.

        -- Rollback any active or uncommittable transactions
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
    END;
END;

GO
SET QUOTED_IDENTIFIER OFF
GO

GO
SET ANSI_NULLS OFF
GO

See Also

List of table/view triggers
Table [HumanResources.Employee]