![]() Simple Insert Update and Delete Triggers in SQL Server with example. In this article I will explain with simple examples, how to write Insert, Update and Delete Triggers in SQL Server. This tutorial is applicable for all versions of SQL Server i. Database. I have made use of the following table Customers with the schema as follows. ![]() Few months back I have written one article which was showing how to store image and other files into SQL Server column by converting it to byte object and receive it. I have already inserted few records in the table. Below is the Customer. Logs table which will be used to log the Trigger actions. ![]() The #1 SQL Server community and education site, with articles, news, forums, scripts and FAQs. Describes cumulative update package 2 for SQL Server 2016 SP1. I am trying to create a trigger each time after data is inserted, updated or deleted. The trigger for inserting is working fine but i am having some issues with the. Note: You can download the database table SQL by clicking the download link below. 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. 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 Customer. Id of the inserted record and the fetched value is inserted in the Customer. Logs table. CREATETRIGGER[dbo].[Customer_INSERT] ON[dbo].[Customers]AFTERINSERTASBEGIN SETNOCOUNTON; DECLARE@Customer. Id. INT SELECT@Customer. Id=INSERTED. Customer. Id FROMINSERTED INSERTINTOCustomer. Logs VALUES(@Customer. Id,'Inserted')ENDUpdate 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 Customer. Id 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 Customer. Id and the appropriate action) is inserted in the Customer. Logs table. CREATETRIGGER[dbo].[Customer_UPDATE] ON[dbo].[Customers]AFTERUPDATEASBEGIN SETNOCOUNTON; DECLARE@Customer. Id. INT DECLARE@Action. VARCHAR(5. 0) SELECT@Customer. Id=INSERTED. Customer. Id FROMINSERTED IFUPDATE(Name) BEGIN SET@Action='Updated Name' END IFUPDATE(Country) BEGIN SET@Action='Updated Country' END INSERTINTOCustomer. Logs VALUES(@Customer. Id,@Action)ENDDelete 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 Customer. Id of the deleted record and the fetched value is inserted in the Customer. Logs table. CREATETRIGGER[dbo].[Customer_DELETE] ON[dbo].[Customers]AFTERDELETEASBEGIN SETNOCOUNTON; DECLARE@Customer. Id. INT SELECT@Customer. Id=DELETED. Customer. Id FROMDELETED INSERTINTOCustomer. Logs VALUES(@Customer. Id,'Deleted')ENDThe following screenshot displays the Log table after the above Triggers were executed. 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 Customer. Id value 2 then such a record must not be deleted and an error must be raised. Also a record is inserted in the Customer. Logs table. If the Customer. Id value is not 2 then a delete query is executed which deletes the record permanently and a record is inserted in the Customer. Logs table. CREATETRIGGER[dbo].[Customer_Instead. Of. DELETE] ON[dbo].[Customers]INSTEADOFDELETEASBEGIN SETNOCOUNTON; DECLARE@Customer. Id. INT SELECT@Customer. Id=DELETED. Customer. Id FROMDELETED IF@Customer. Id= 2 BEGIN RAISERROR('Mudassar Khan''s record cannot be deleted',1. ROLLBACK INSERTINTOCustomer. Logs VALUES(@Customer. Id,'Record cannot be deleted.') END ELSE BEGIN DELETEFROMCustomers WHERECustomer. Id=@Customer. Id INSERTINTOCustomer. Logs VALUES(@Customer. Id,'Instead Of Delete') ENDENDThe following error message shown when record with Customer. Id 2 is deleted. The following screenshot displays the Log table after the Instead Of Trigger is executed.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |