Description
INSTEAD OF DELETE trigger which keeps Employees from being deleted.
Trigger properties
| name | value |
|---|
| parent | Employee |
| type | Instead of Delete |
| is first insert | false |
| is last insert | false |
| is first update | false |
| is last update | false |
| is last delete | false |
| is first delete | false |
| is disabled | false |
| is not for replication | true |
Dependency graph
Objects that [HumanResources].[dEmployee] depends on
| name | object type | database | server | level |
|---|
| Employee | table | AdventureWorks2008 | SPRING\KATMAI | 1 |
| Flag | type | AdventureWorks2008 | SPRING\KATMAI | 2 |
Code
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;