Instance of trigger practice for SQL Server

The concept of a trigger: It is event-driven, like a listener in Java, and when an event occurs, it does some work.

Following the dry goods directly, create an INSERT trigger, a DELETE trigger, a DDL trigger, and how to view the trigger definition

1. Create three Tables student table, class table, curriculum

Create DATABASE student_scorego--The structure of the three Table student table, class table, curriculum in the Databases Use Student_scoregocreate table student (stu_id char (8) Primary key,  Stu_name char (Ten),  Stu_sex char (2),  stu_birthday smalldatetime,  class_id char (6)) Gocreate Table Class (  class_id char (6) Primary key,  class_name varchar (+), Class_num int,) CREATE TABLE course ( course_id char (3) primary key,  course_name varchar (+),) Go CREATE TABLE score (stu_id char (8),  course_id char (3) ,  score int check (score>=0 and score<=100)  primary KEY (stu_id,course_id)) go

2. Inserting use case data

Insert data into table (Student,course,score) insert into student values (' 0601001 ', ' Li ', ' female ', ' 1987-05-06 ', ' 0601 ') insert INTO Student values (' 0601002 ', ' h ', ' female ', ' 1988-06-28 ', ' 0601 ') insert into student values (' 0601003 ', ' Li Xiao ', ' female ', ' 1987-01-08 ', ' 0601 ') insert into student values (' 0601004 ', ' Rubin ', ' Male ', ' 1988-04-21 ', ' 0601 ') insert into student values (' 0601005 ', ' King Serene ', ' Female ', ' 1986-05-29 ', ' 0601 ') insert into student values (' 0601006 ', ' Ms Cheung ', ' Male ', ' 1987-02-24 ', ' 0601 ') insert INTO student VALUES (' 0601007 ', ' Liu Xiaoling ', ' female ', ' 1988-12-21 ', ' 0601 ') insert into student values (' 0601008 ', ' Zhou Xiao ', ' Male ', ' 1986-04-27 ', ' 0601 ') INSERT into student values (' 0601009 ', ' Ishuriang ', ' Male ', ' 1985-11-26 ', ' 0601 ') insert into student values (' 0601010 ', ' monsoon ', ' Male ', '  1986-09-21 ', ' 0601 ') insert into class values (' 0501 ', ' Computer Office application ', ' Max ') insert into class values (' 0502 ', ' network build ', +) insert INTO Class values (' 0503 ', ' graphic image ', ' + ') insert into class values (' 0601 ', ' visualize ', ') insert into class values (' 0602 ', ' database ', ' Inser ') T into class values (' 0603 ', ' Network management ', ' + ') insert into class values(' 0604 ', ' multimedia ', ' + ') insert into class values (' 0701 ', ' Computer Office applications ', ") insert into class values (' 0702 ', ' Web application ', ' Max ') insert INTO C Lass values (' 0703 ', ' Network Build ', ' Max ') insert into course values (' 001 ', ' Computer Application Basics ') insert into course values (' 002 ', ' relational data base ') Insert into course values (' 003 ', ' Programming basics ') insert into course values (' 004 ', ' data structure ') insert into course values (' 005 ', ' web Design ') Insert into course values (' 006 ', ' web Design ') insert into course values (' 007 ', ' SQL Server 2000 relational database ') insert into course values (' 008 ', ' SQL Server 2000 programming ') insert into course values (' 009 ', ' computer network ') insert into course values (' 010 ', ' Windows server Configuration ') INSERT into score values (' 0601001 ', ' 001 ', ' + ') insert into score values (' 0601002 ', ' 001 ', ",") insert into score values (' 0601003 ', ' 001 ', ' + ') insert into score values (' 0601004 ', ' 001 ', ") insert into score values (' 0601005 ', ' 001 ', ' + ') insert INTO Score values (' 0601006 ', ' 001 ', and all) insert into score values (' 0601007 ', ' 001 ', ") insert into score values (' 0601008 ', ' 001 ', INSERT into score values (' 0601009 ', ' 001 ', 98) insert INTO Score values (' 0601010 ', ' 001 ', ') insert into score values (' 0601001 ', ' 002 ', ') insert into score values (' 0601002 ', ' 002 ', () insert INTO score values (' 0601003 ', ' 002 ', ") insert into score values (' 0601004 ', ' 002 ',") insert into score values (' 0601005 ', ' 002 ', () insert into score values (' 0601006 ', ' 002 ', ") insert into score values (' 0601007 ', ' 002 ', a ') insert into Score values (' 0601008 ', ' 002 ', ") insert into score values (' 0601009 ', ' 002 ', 98 ') INSERT into score values (' 0601010 ', ' 002 ', INSERT into score values (' 0601001 ', ' 003 ', ") insert into score values (' 0601002 ', ' 003 ',") insert into score values (' 0601003 ', ' 003 ', ' + ') insert into score values (' 0601004 ', ' 003 ', ' a ') insert into score values (' 0601005 ', ' 003 ', ") insert INTO Score values (' 0601006 ', ' 003 ', and all) insert into score values (' 0601007 ', ' 003 ', ') insert into score values (' 0601008 ', ' 003 ', INSERT into score values (' 0601009 ', ' 003 ', 98) insert INTO score values (' 0601010 ', ' 003 ', ") insert into score values (' 0601001 ', ' 004 ', () insert into score values (' 0601002 ', ' 004 ', 68INSERT into score values (' 0601003 ', ' 004 ', up) insert into score values (' 0601004 ', ' 004 ', ") insert into score values (' 0601005 ', ' 004 ', ', ') insert into score values (' 0601006 ', ' 004 ', ") insert into score values (' 0601007 ', ' 004 ', a ') insert into Score values (' 0601008 ', ' 004 ', ") insert into score values (' 0601009 ', ' 004 ', 98 ') INSERT into score values (' 0601010 ', ' 004 ', () insert INTO score values (' 0601001 ', ' 005 ', ") insert into score values (' 0601002 ', ' 005 ',") insert into score values (' 0601005 ', ' 005 ', () insert into score values (' 0601008 ', ' 005 ', ") insert into score values (' 0601009 ', ' 005 ', 98) insert INTO Score values (' 0601010 ', ' 005 ', ') insert into score values (' 0601002 ', ' 006 ', ') inserts into score values (' 0601003 ', ' 006 ', INSERT into score values (' 0601006 ', ' 006 ', and a ') insert into score values (' 0601008 ', ' 006 ', ") insert into score values (' 0601010 ', ' 006 ', ') insert into score values (' 0601001 ', ' 007 ', ' + ') insert into score values (' 0601002 ', ' 007 ', ") insert INTO Score values (' 0601003 ', ' 007 ', up) insert into score values (' 06010(' 008 ', ') insert into score values (' 0601005 ', ' 008 ', ") insert into score values (' 0601006 ', ' 008 ',") insert INTO score VALUES (' 0601007 ', ' 009 ', ') insert into score values (' 0601008 ', ' 009 ', ") insert into score values (' 0601009 ', ' 010 ', 98) INSERT into score values (' 0601010 ', ' 010 ', 75)

3. Practice examples

--1) created on studentInsert TriggerStu_insert, this trigger updates the Class_nun column in the class table when a record is required to be inserted in the student table (only one record can be inserted at a time). and test the trigger Stu_insert. Create Trigger Stu_inserton Studentfor Insertasupdate class set Class_num=class_num + 1 where class_id= (select class_id fr Om inserted)--test insert into student values (' 0602011 ', ' text ', ' female ', ' 1986-09-21 ', ' 0602 ')--2) created on studentDelete TriggerStu_delete, this trigger updates the Class_nun column in the class table when a record is required to be deleted in the student table. and test the trigger stu_delete. Create Trigger Stu_deleteon Studentfor Insertasupdate class set Class_num=class_num-1where class_id = (select class_id F Rom deleted)--test Delete from student where stu_id= ' 0601001 '--3) view trigger related information: using system stored proceduresSp_help,sp_helptext viewing triggersRelated information. EXEC sp_helpexec sp_help stu_insertexec sp_helptext stu_insert--4) for the following triggers: Create TRIGGER Stu_updateon Studentinstead of Updateasprint ' modify student table ' drop trigger stu_update EXECUTE statement update studentset stu_id= ' 0601003 ' where stu_name= ' Rubin ' what happens? --Msg 2627, Level 14, State 1, line 1th--violates the PRIMARY KEY constraint ' pk__student__e53cab217f60ed59 '. You cannot insert a repeating key in the object ' Dbo.student '. --The statement has been terminated. --5) CreateDDL triggers, deleting or modifying tables is not allowed in the current database create trigger Dataon databasefor drop_table, Alter_tableasprint ' do not allow delete or modify table ' rollback

This example is complete for a friend who needs a practice trigger to see

Instance of trigger practice for SQL Server