Triggers In SQL


create table tblFees
(
FID int identity(100,1) primary key,
RollNo varchar(20),
FullName varchar(50),
Phone varchar(20),
Fees int,
BirthDate dateTime
);
select * from tblFees

——————————
–Trigger Syntax
create trigger TRIGGER_NAME on TABLE_NAME for Insert as
if(Select COLUMN_NAME from Inserted)<5000
begin
print ‘Error: Fees must be more than 5000 Rs.’
RollBack Transaction
end;

——————————-
— Company Policies or Business Rules.
–01: Fees must be more than 5000Rs.
–02: Phone Number atleast 11-Digits.
–03: FullName atleast 5-Alphabats.
–04: Age must be 18 or greater.
——————————-
–Trigger for Rule 01:
create trigger trgFees on tblFees for Insert as
if(Select Fees from Inserted)<5000
begin
print ‘Error: Fees must be more than 5000 Rs.’
RollBack Transaction
end;
——————————–
–Trigger for Rule 02:
create trigger trgPhone on tblFees for Insert as
if(Select Len(Phone) from Inserted)<11
begin
print ‘Error: Invalid Phone Number. [At least 11-Digit]’
RollBack Transaction
end;
——————————–
–Trigger for Rule 03:
create trigger trgName on tblFees for Insert as
if(Select Len(FullName) from Inserted)<6
begin
print ‘Error: Invalid Name! Enter FullName.’
RollBack Transaction
end;
——————————–
–Trigger for Rule 04:
create trigger trgAge on tblFees for Insert as
Declare @BDT DateTime
Select @BDT = BirthDate from Inserted
Declare @Age int = DateDiff(Year,@BDT,GetDate())
if(@Age>=18 and @Age<=40)
begin
print ‘Data Saved…’
Commit Transaction
end;
else
begin
print ‘Error: Invalid Age! Only [18-40] years of Age allowed.’
RollBack Transaction
end;
——————————–
–For Disable Trigger
Disable Trigger trgFees on tblFees;

–For Enable Trigger
Enable Trigger trgFees on tblFees;

Insert into tblFees values (‘Student789654′,’Refrencee’,’03001234567′,’3000′,’01-01-1990′);
Select * from tblFees;

Advertisements

It's Great To Hear From You

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s