trigger
1. Define trigger
CREATE TRIGGER Syntax format
CREATE TRIGGER <Trigger Name >
{BEFORE | AFTER} <Trigger event> ON <Table name>
REFERENCING NEW|OLD ROW AS<variable>
FOR EACH {ROW | STATEMENT}
[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:
UPDATE TEACHER SET Deptno=5;
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 referencing 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) values(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade);
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.
T-Sql
create trigger Sc_t on SC after update as begin 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) values(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade);
[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;
T-Sql
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;
implement
insert into Student
values('20151167','My','male',12,'CS');
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 begin if(new.Job='professor')and(new.sal<4000) then new.Sal :=4000; end if; end;
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.
advantage:
(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
as
begin transaction trans
declare
@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
begin
print 'The transfer out account does not exist or the account balance is 0'
rollback transaction trans; --Rollback transaction
return
end ;
if @totalDepositOut < @amount --2.Insufficient account balance
begin
print 'Insufficient account balance'
rollback transaction trans;
return;
end
select @inAccount =accountnum from Account
where accountnum = @inAccount;
if @inAccount is null
begin
print 'Transfer in account does not exist'
rollback transaction trans;
return ;
end ;
begin
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 ;
end;(2) Execute stored procedure
[example 8.9] transfer 10000 yuan from account 01003815868 to account 01003813828.
sql
call procedure transfer (01003813828,01003815868,10000);
T-sql
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()；