How to Update and Delete Trigger in SQL
In this article I am going to explain how to update and delete a trigger in SQL.
Introduction
Triggers in SQL are a special type of procedure that are executed, fired or invoked automatically in response to an action query are executed on a view or table. We can set trigger on insertion, deletion and updating or combination of these operation.
There are three type of trigger in SQL.
- AFTER Trigger
- INSTEAD OF Trigger
- FOR Trigger
There are many situation when we need to update any trigger ALTER TRIGGER statement is used to update a trigger in SQL. We use DROP TRIGGER statement to delete any trigger.
A Statement that create mcnvendors table
create table mcnvendors
(
vendorid int,
vendorname varchar(15),
vendorcity varchar(15),
vendorstate varchar(15)
)
|
Statements that insert data in mcnvendors table
insert into mcnvendors values (20,'vipendra','noida','up')
insert into mcnvendors values (21,'deepak','lucknow','up')
insert into mcnvendors values (22,'rahul','kanpur','up')
insert into mcnvendors values (23,'malay','delhi','delhi')
insert into mcnvendors values (24,'mayank','noida','up')
|
A Statement that is used to fetch data from mcnvendors table
A Statement that is used to create a trigger
create trigger mcnven_trig
on mcnvendors
after insert,update
as
update mcnvendors
set vendorstate = upper(vendorstate)
where vendorid IN
(select vendorid from inserted)
|
A Statement that is used to update a trigger
alter trigger mcnven_trig
on mcnvendors
after insert,update
as
update mcnvendors
set vendorstate = upper(vendorstate),
vendorcity = ltrim(rtrim(vendorcity))
where vendorid IN
(select vendorid from inserted)
|
A Statement use trigger
A Statement that show data from vendor table
A Statement that is used to delete a trigger
drop trigger copymcninvoices_ins_up |