Repository navigation
Expand file tree
/
Copy pathTriggers.sql
More file actions
102 lines (90 loc) · 2.39 KB
/
Copy pathTriggers.sql
File metadata and controls
102 lines (90 loc) · 2.39 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_Employee_AutoUpdateDate]
ON [dbo].[Employee]
AFTER UPDATE
AS
BEGIN
UPDATE Employee
SET UpdateDate = GETDATE()
FROM Employee e
INNER JOIN inserted i ON e.WorkerID = i.WorkerID;
END;
GO
ALTER TABLE [dbo].[Employee] ENABLE TRIGGER [trg_Employee_AutoUpdateDate]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_CheckBudgetOverflow]
ON [dbo].[Profit]
AFTER UPDATE, INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ProjectID INT;
DECLARE @TotalExpenses DECIMAL(18,2);
DECLARE @Budget DECIMAL(18,2);
DECLARE @ProjectName NVARCHAR(255);
SELECT @ProjectID = i.ProjectID, @TotalExpenses = i.TotalExpenses FROM inserted i;
SELECT @Budget = Budget, @ProjectName = ProjectName FROM Project WHERE ProjectID = @ProjectID;
IF @Budget IS NULL RETURN;
IF @TotalExpenses > @Budget
BEGIN
DECLARE @ErrorMessage NVARCHAR(500);
SET @ErrorMessage =
'Budget Overflow! Project "' + ISNULL(@ProjectName, 'Unknown') + '" (ID: ' + CAST(@ProjectID AS VARCHAR(10)) + ')' +
' - Budget: $' + CAST(@Budget AS VARCHAR(20)) +
', Total Expenses: $' + CAST(@TotalExpenses AS VARCHAR(20)) +
', Overflow: $' + CAST((@TotalExpenses - @Budget) AS VARCHAR(20));
RAISERROR(@ErrorMessage, 16, 1);
ROLLBACK TRANSACTION;
END
END;
GO
ALTER TABLE [dbo].[Profit] ENABLE TRIGGER [trg_CheckBudgetOverflow]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_PreventDeleteCompletedProject]
ON [dbo].[Project]
INSTEAD OF DELETE
AS
BEGIN
DECLARE @Status NVARCHAR(20);
SELECT @Status = ProjectStatus FROM deleted;
IF @Status = 'Completed'
BEGIN
RAISERROR('Completed projects can not be deleted.', 16, 1);
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
DELETE FROM Project WHERE ProjectID IN (SELECT ProjectID FROM deleted);
END
END;
GO
ALTER TABLE [dbo].[Project] ENABLE TRIGGER [trg_PreventDeleteCompletedProject]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_Project_AutoUpdateDate]
ON [dbo].[Project]
AFTER UPDATE
AS
BEGIN
UPDATE Project
SET UpdateDate = GETDATE()
FROM Project p
INNER JOIN inserted i ON p.ProjectID = i.ProjectID;
END;
GO
ALTER TABLE [dbo].[Project] ENABLE TRIGGER [trg_Project_AutoUpdateDate]
GO