Sql Server 12: trigger + stored procedure

Posted by HokieTracks on Fri, 04 Mar 2022 03:31:22 +0100


1. Define trigger

CREATE TRIGGER Syntax format
	   CREATE TRIGGER <Trigger Name >  
       {BEFORE | AFTER} <Trigger event> ON <Table name>
       [WHEN <Trigger condition>]<Trigger action body>

When a specific system event occurs, the condition of the rule is checked.

If the condition is true, the action in the rule will be executed; otherwise, the action will not be executed.

The action body in a rule can be very complex, usually an SQL stored procedure

2. Trigger event

  • The trigger event can be INSERT, DELETE or UPDATE

It can also be a combination of these events

  • You can also update of < trigger column,... >, That is, it further indicates which columns are modified to activate the trigger
  • AFTER/BEFORE is the trigger time

AFTER indicates that the trigger is activated AFTER the operation that triggers the event is executed

BEFORE indicates that the trigger is activated BEFORE the action that triggers the event is executed


3. Trigger type

  • Row level trigger (FOR EACH ROW)
  • Statement level trigger (for each state)

For example, create an AFTER UPDATE trigger on the TEACHER table in example 5.11. The trigger event is an UPDATE statement:


Suppose the table tea has 1000 rows

  • If it is a statement level trigger, the trigger action only occurs once after the statement is executed
  • If it is a row level trigger, the trigger action will be executed 1000 times

Note: different RDBMS products have different trigger syntax


[example 5.21] when modifying the Grade attribute of table SC, if the score increases by 10%, record the operation in the following table:

create trigger Sc_t
after update of Grade on SC
	old row as OldTuple,
	new row as NewTuple
for each row
when (NewTuple.Grade>=1.1*OldTuple.Grade)
insert into SC_U(Sno,Cno,OldGrade,NewGrade)

If the trigger condition is update and the for each row clause is used, the variables that can be referenced are oldrow and newrow, which respectively represent the tuples before and after modification.


create trigger Sc_t
on SC after update 
  declare @OldTuple int;
  declare @NewTuple int;
select @OldTuple= Grade from inserted
select @NewTuple=Grade from inserted;
when (NewTuple.Grade>=1.1*OldTuple.Grade)
insert into SC_U(Sno,Cno,OldGrade,NewGrade)

[example 5.22] record the number of students added by each insert operation on the table Student into the table StudentInsertLog.

create trigger Student_Count
after insert on Student
 new table as Delta
 for each statement
 insert into Studentinsertlog(Numbers)
 select count(*) from delta;



create table StudentInsertLog(
Numbers int
--Remember to write separately, or you will report an error like the following

create trigger Student_Count
on Student
after insert
as insert into StudentInsertLog(Numbers)
select count(*) from Student;


insert into Student

select * from  StudentInsertLog;


[example 5.23] define a BEFORE row level trigger to define the integrity rule for the Teacher table Teacher: "the salary of a professor shall not be less than 4000 yuan. If it is less than 4000 yuan, it will be automatically changed to 4000 yuan".

create trigger insert_or_Update_Sal
before insert or update on Teacher
for each row
then new.Sal :=4000;
end if;

Note the "=" of standard sql

4. Activate trigger

The execution of the trigger is activated by the trigger event and automatically executed by the database server!

Multiple triggers may be defined on a data table, following the following execution sequence:

(1) Execute the BEFORE trigger on the table;

(2) Activate the SQL statement of the trigger;

(3) Execute the AFTER trigger on the table.

5. Delete trigger

DROP TRIGGER <Trigger Name > ON <Table name>;


Stored procedures and functions

1. Stored procedure

Stored procedure: procedural SQL statements are compiled and optimized and stored in the database server. They can be called repeatedly and run faster.


(1) High operation efficiency

(2) Reduced traffic between client and server

(3) Facilitate the implementation of enterprise rules



(1) Create stored procedure

CREATE OR REPLACE PROCEDURE Process name([Parameter 1,Parameter 2,...]) AS <Processization SQL block>;

[example 8.8] use the stored procedure to realize the following application: transfer the specified amount of money from account 1 to account 2.

(I don't know where the error prompt here is that the column name accountnum is invalid. There are some in the Account table...)

create procedure Proc_TRANSFER (
--Define stored procedures as transfer
@inAccount int,
@outAccount int,
@amount float)
--Formal parameter
begin transaction trans
@totalDepositOut float,--Define variables
@totalDepositin float,
@inAccountum int;

select @totalDepositOut=total from Account where accountnum = @outAccount;
if @totalDepositOut is null --1.If there is no transfer out account
    print 'The transfer out account does not exist or the account balance is 0'
rollback transaction trans; --Rollback transaction
end ;
if @totalDepositOut < @amount --2.Insufficient account balance
 print 'Insufficient account balance'
rollback transaction trans;
select @inAccount =accountnum from Account
where accountnum = @inAccount;
if @inAccount is null
print 'Transfer in account does not exist'
rollback transaction trans;
return ;
end ;
update Account set total =total-@amount
where accoutnum =@outAccount;
update account set total = total+@amount
where accountnum =@inAccount;
print 'Transfer succeeded'
commit transaction trans;
return ;


(2) Execute stored procedure

[example 8.9] transfer 10000 yuan from account 01003815868 to account 01003813828.


call procedure
transfer (01003813828,01003815868,10000);


exec	Proc_TRANSFER
		@inAccount = 01003813828,	--Transfer into account
		@outAccount = 01003815868,	--Transfer out account
		@amount = 10000     --Transfer out amount

select * from Account;

(3) Modify stored procedure

    ALTER PROCEDURE Process name 1  RENAME TO Process name 2;

(4) Delete stored procedure

DROP  PROCEDURE Process name();
























Topics: SQL