Saturday, November 11, 2017

T-SQL SQL Server TRIGGERS

To quickly get a list of all CONSTRAINTS on tables in a database:

SELECT * FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'

See: http://itperfecting.blogspot.com/2018/01/to-view-constraints-in-sql-server.html

Superb article:

https://www.aspsnippets.com/Articles/Simple-Insert-Update-and-Delete-Triggers-in-SQL-Server-with-example.aspx


(copied for posterity)

Triggers
Triggers are database operations which are automatically performed when an action such as Insert, Update or Delete is performed on a Table or a View in database.
Triggers are associated with the Table or View directly i.e. each table has its own Triggers.
Types of Triggers
There are two types of Triggers. After and Instead of Triggers.
After Triggers
These triggers are executed after an action such as Insert, Update or Delete is performed.
Instead of Triggers
These triggers are executed instead of any of the Insert, Update or Delete operations. For example, let’s say you write an Instead of Trigger for Delete operation, then whenever a Delete is performed the Trigger will be executed first and if the Trigger deletes record then only the record will be deleted.
After Triggers
Now I will explain you with examples the After Triggers for Insert, Update and Delete operations.
Insert Trigger
Below is an example of an After Insert Trigger. Whenever a row is inserted in the Customers Table, the following trigger will be executed. The newly inserted record is available in the INSERTED table.
The following Trigger is fetching the CustomerId of the inserted record and the fetched value is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_INSERT]
       ON [dbo].[Customers]
AFTER INSERT
AS
BEGIN
       SET NOCOUNT ON;
       DECLARE @CustomerId INT
       SELECT @CustomerId = INSERTED.CustomerId       
       FROM INSERTED
       INSERT INTO CustomerLogs
       VALUES(@CustomerId, 'Inserted')
END
Update Trigger
Below is an example of an After Update Trigger. Whenever a row is updated in the Customers Table, the following trigger will be executed. The updated record is available in the INSERTED table.
The following Trigger is fetching the CustomerId of the updated record. In order to find which column is updated, you will need to use UPDATE function and pass the Column name of the Table to it.
The UPDATE function will return TRUE for a Column if its value was updated else it will return false.
Finally based on which column of the record has been updated a record (containing the CustomerId and the appropriate action) is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_UPDATE]
       ON [dbo].[Customers]
AFTER UPDATE
AS
BEGIN
       SET NOCOUNT ON;
       DECLARE @CustomerId INT
       DECLARE @Action VARCHAR(50)
       SELECT @CustomerId = INSERTED.CustomerId       
       FROM INSERTED
       IF UPDATE(Name)
       BEGIN
              SET @Action = 'Updated Name'
       END
       IF UPDATE(Country)
       BEGIN
              SET @Action = 'Updated Country'
       END
       INSERT INTO CustomerLogs
       VALUES(@CustomerId, @Action)
END
Delete Trigger
Below is an example of an After Delete Trigger. Whenever a row is delete in the Customers Table, the following trigger will be executed. The deleted record is available in the DELETED table.
The following Trigger is fetching the CustomerId of the deleted record and the fetched value is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_DELETE]
       ON [dbo].[Customers]
AFTER DELETE
AS
BEGIN
       SET NOCOUNT ON;
       DECLARE @CustomerId INT
       SELECT @CustomerId = DELETED.CustomerId       
       FROM DELETED
       INSERT INTO CustomerLogs
       VALUES(@CustomerId, 'Deleted')
END
The following screenshot displays the Log table after the above Triggers were executed.
Simple Insert Update and Delete Triggers in SQL Server with example
Instead Of Triggers
Below is an example of an Instead Of Delete Trigger. Whenever anyone tries to delete a row from the Customers table the following trigger is executed.
Inside the Trigger, I have added a condition that if record has CustomerId value 2 then such a record must not be deleted and an error must be raised. Also a record is inserted in the CustomerLogs table.
If the CustomerId value is not 2 then a delete query is executed which deletes the record permanently and a record is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_InsteadOfDELETE]
       ON [dbo].[Customers]
INSTEAD OF DELETE
AS
BEGIN
       SET NOCOUNT ON;
       DECLARE @CustomerId INT
       SELECT @CustomerId = DELETED.CustomerId       
       FROM DELETED
       IF @CustomerId = 2
       BEGIN
              RAISERROR('Mudassar Khan''s record cannot be deleted',16 ,1)
              ROLLBACK
              INSERT INTO CustomerLogs
              VALUES(@CustomerId, 'Record cannot be deleted.')
       END
       ELSE
       BEGIN
              DELETE FROM Customers
              WHERE CustomerId = @CustomerId
              INSERT INTO CustomerLogs
              VALUES(@CustomerId, 'Instead Of Delete')
       END
END
The following error message shown when record with CustomerId 2 is deleted.
Simple Insert Update and Delete Triggers in SQL Server with example
The following screenshot displays the Log table after the Instead Of Trigger is executed.
Simple Insert Update and Delete Triggers in SQL Server with example



No comments: