Trigger: [AdventureWorks2016CTP3].[HumanResources].[dEmployee] on Table Employee

CollapseAll image

Collapse image Trigger properties


Name  Value 
 Schema   [HumanResources] 
 Owner   [dbo] 
 Creation date   16.11.2015 
 Encrypted   
 Disabled   
 ID   861246123 
 Implementation type   Transact SQL 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Type


Instead of Insert Update Delete
   

Collapse image Objects that [HumanResources].[dEmployee] depends on


Object name Object type Dep level
[HumanResources] Schema 1
[HumanResources].[Employee] Table 1
Total 2 object(s)

Collapse image Extended properties


Name  Value 
MS_Description INSTEAD OF DELETE trigger which keeps Employees from being deleted.

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER 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
EXEC sp_addextendedproperty N'MS_Description', N'INSTEAD OF DELETE trigger which keeps Employees from being deleted.', 'SCHEMA', N'HumanResources', 'TABLE', N'Employee', 'TRIGGER', N'dEmployee'
GO

Collapse image See also


List of triggers

[HumanResources].[Employee]