Course Notes
Use this workspace to take notes, store code snippets, and build your own interactive cheatsheet!
# Import any packages you want to use here
Take Notes
Add notes here about the concepts you've learned and code cells with code you want to keep.
Triggers are a good way to execute additional actions when changes occur in your database. Triggers can also be used to prevent changes and execute different actions instead.
Examples : To send an email to the Sales team when a new order is added to the Orders table To copy the modified rows to a history table when an update occurs on the Products table To deny the creation of a new database
The previous step used both a computed column and a trigger to calculate the TotalAmount value automatically. From a user perspective, there was no difference, but from a technical perspective, there is one. What is the major limitation of computed columns that can be easily overcome with the use of triggers? A computed column cannot use columns from other tables for the calculation.
Always check your trigger is working as expected after you create it.
Add your notes here
TRIGGERS LIMITATIONS
Triggers are invisible to client applications and difficult to debug.
Triggers are difficult to test and troubleshoot when they are too complex.
Triggers can degrade the server's performance when overused.
-- Add a trigger that tracks table changes
CREATE TRIGGER OrdersAudit
ON Orders
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @Insert BIT = 0;
DECLARE @Delete BIT = 0;
IF EXISTS (SELECT * FROM inserted) SET @Insert = 1;
IF EXISTS (SELECT * FROM deleted) SET @Delete = 1;
INSERT INTO TablesAudit (TableName, EventType, UserAccount, EventDate)
SELECT 'Orders' AS TableName
,CASE WHEN @Insert = 1 AND @Delete = 0 THEN 'INSERT'
WHEN @Insert = 1 AND @Delete = 1 THEN 'UPDATE'
WHEN @Insert = 0 AND @Delete = 1 THEN 'DELETE'
END AS Event
,ORIGINAL_LOGIN() AS UserAccount
,GETDATE() AS EventDate;Trigger sbest practice Tips:
- well-documented database design
- simple logic in trigger design
- avoid over using triggers
use cases of instead of triggers prevent operations from happening Control database statements Enforce data integrity
-- Create a new trigger
CREATE TRIGGER DatabaseAudit
-- Attach the trigger at the database level
ON database
-- Fire the trigger for all tables/ views events
FOR DDL_TABLE_VIEW_EVENTS
AS
INSERT INTO DatabaseAudit (EventType, DatabaseName, SchemaName, Object, ObjectType, UserAccount, Query, EventTime)
SELECT EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(50)') AS EventType
,EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(50)') AS DatabaseName
,EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(50)') AS SchemaName
,EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(100)') AS Object
,EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]', 'NVARCHAR(50)') AS ObjectType
,EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)') AS UserAccount
,EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)') AS Query
,EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME') AS EventTime;-- Create a trigger to prevent database deletion
CREATE TRIGGER PreventDatabaseDelete
-- Attach the trigger at the server level
ON ALL SERVER
FOR DROP_DATABASE
AS
PRINT 'You are not allowed to remove existing databases.';
ROLLBACK;