Articles

SQL Server interview training :-What are triggers , inserted and deleted tables ?


In this video will talk about what exactly are triggers, and in what scenario we should be using them While we do that will talk about two important types of tables one is called as inserted table and the other one is called as deleted table Will talk about two different kinds of triggers i.e called as the Instead of trigger and the After trigger Triggers are logic like stored procedures which can be executed before the updation happens on a table or after the updation happens on a table If you want to execute pre processing or post processing logic after the updation or before the updation you can use triggers There are two kinds of triggers, one is the Instead of trigger and the other one is After triggers Instead of trigger executes before the updation happens on a table After trigger executes after the updation has already happen on a table Lets demonstrate what is trigger and then will demonstrate about inserted and deleted tables I have created a very simple table called as tblCustomer This tblCustomer table has two fields here one is the customer Id and the other one is the customer name Will write a very simple trigger here which will fire every insert, update and delete operations on this tblCustomer table That trigger will update a audit table In Audit table we will record the time on which that insert, update or delete operation happened Create a very simple audit table first Click on new table here and in this audit table will create a field called as last updated time This LastUpdatedTIme field will have the date and time when the insert, update or the delete operation actually happened Name this table as audit Can put some good naming convention tblAudit Let’s create a trigger on this tblCustomer table In order to create a trigger click on the plus sign There is a folder of triggers Expand this folder Right click on this folder click on new trigger Once you click on the new trigger menu He has opened up a templated code here By using templated code we can create a new trigger The way the sysntax goes for creating a trigger First write create and then trigger then trigger name On which table the trigger will be executed On what kind of statements the trigger will be executed It will need three things Trigger name Table on which the trigger will be executed On what kind of operations the trigger will be executed Create a simple trigger and give a name as Trigger Update This trigger will fire on the table tblCustomer This trigger should be executed on Insert, update as well as delete Now we need write the code which will execute once this triggers fires In this trigger will make a entry into the tblAudit table with the date and time in which this insert update and delete operation has happened In this trigger will write simple logic here insert into this tblAudit table Insert into this field LastUpdatedTime The current date and time we can get by using this GETDATE (). This GETDATE function helps us to get the current date and time from the system We have created a trigger called as TriggerUpdate And this TriggerUpdate will execute on the table called as a tblCustomer It will execute on all the operation Whenever a insert happens or a delete happens or a update happens it will fire a simple insert onto the tblAudit table with a current date and time Execute this create syntax He has said command is successfully completed If you refresh this triggers folder He has created a simple trigger called as TriggerUpdate Go to the tblCustomer table and fire some inserts first As soon as we fire the inserts, it should make a entry into the tblAudit table In this tbl Audit table there are no records. The LastUpdatedTime is nothing As we go and make a entry into this tblCustomer table I should start getting some records here In my Audit table he have made one entry with the date and time when this insert happened In the same way I will make one more entry of record If I fire this select statement, you can see one more entry If I delete this This trigger was enabled for insert, update as well as delete I should find one record entry of record If I update this record It shows me one more entry of record Currently our logic is we have to insert into the tblAudit table They are logic which actually fires on every insert, update an delete operation which happens on the table This example is a very lean example When I make a Insert, update or delete here, he just inserts the updated date and time This is not a good audit information The old and the new value. The old value is Shiva and if somebody update it to Shiv Then I would like to see record here This is the last updated date and time The old value was Shiva and the new value is Shiv I would like to add some more information to this audit table to make it more reasonable In order to do this you need to have access to the old records as well as the new records Whenever I update this value the trigger from somewhere should know this was the old record and this was a new record He can make appropriate entries into this audit table We have two kinds of temporary tables created by SQL server One is called as a Inserted table and the other one is called as the deleted table In order to understand the concept of Inserted and deleted tables Whenever you fire any insert, update or delete query on the table All the new records go to the inserted table, all the updated and the new records are present in the inserted table All the old values are present in the deleted tables For example you have Shiv and you want to update to Shiva If you do that, the old value i.e Shiv will be present in the deleted table The new value i.e Shiva will be presented in the inserted table Will take both these tables and will try to add these two information i.e a old value and new value into the audit table Will modify our audit table structure and will create two fields, one is a old value and new value will try to fetch those values by using this two templates over there i.e inserted and deleted Go to SQL server back again Open the tblAudittable in the design mode I will add in our tblCustomer table we have one column called as customer name I will add two fields here, one is the OldCustomerName Other one is a NewCustomerName Whenever any updation happens on the table we will fetch the old values into this field and will fetch the new values into this field over here The old values will get from the deleted tables and the new value will get from the inserted table Go to the trigger Take the old values and new values from the inserted and deleted tables Declare two variables here One variable as old value Other variable NewValue which will be fetch from the inserted table We need to fetch the old values from the deleted table The new value from the inserted table Inserted and deleted tables are temporary tables which are created by SQL server The table structure of inserted and deleted are same as the table on which you are doing transaction Currently I am doing transaction on tblCustomer The table structure of tblCustomer is CustomerId and CustomerName The inserted and the deleted table will also have the same table structure They will have two columns, one is customer id and the other one is customer name Fetch the old value first and then fetch the the new values In order to fetch the old value will query the deleted table From the deleted table This will fetch the current updated value or the new value from the deleted table For the new values will query the inserted table Both these value we will insert into the Audit table In the Audit table we have two columns One is the CustomerName OldCustomerName and the second one is the NewCustomerName Will fetch into OldCustomerName The OldValue In the NewCustomerName will fetch the NewValue Go to the customer table and edit the CustomerTable First Insert a record Go to the AuditTable Because this is a new record the OldValue is NULL and the new value is Raju Update Raju to Raja If we execute the query now, The OldCustomerName was Raju The NewCustomerName is Raja and it was updated at this time If I delete Raja If I go back to AuditTable you can see the OldCustomerName was Raja and it was deleted This Null value in the new signifies there is no new value By using the inserted and the deleted tables how we are able to fetch the Old and New values We started this video talking about what exactly are triggers. Triggers are logics which gets executed after or before the insertion or deletion or updation happens on a table Second we talked about temporary two table one is the inserted table and the other one is the deleted table Inserted tables have the new values and the deleted tables have the old values where we used both these tables to keep Audit trail of the old and the new values There are two more important concepts in triggers and those are types of triggers There are two kind of triggers one is the Instead of trigger and the other one is the After trigger In the next video will talk about what exactly is Instead of trigger and After Trigger and will also see a simple sample demonstration of Instead of and After Triggers from practical aspects I hope you enjoyed this video. In the next video will talk about Instead of Trigger and After Trigger Thank you so much

Leave a Reply

Your email address will not be published. Required fields are marked *