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.
  • 2018

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

ven (2).jpg

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

Clipboard26666666666.jpg

A Statement that show data from vendor table

Clipboard288888.jpg

A Statement that is used to delete a trigger

drop trigger copymcninvoices_ins_up

© 2019 DotNetHeaven. All rights reserved.