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