数据库总结九触发器

在SQL Server 2005中，触发器可以分为两大类：DML触发器和DDL触发器

  DML触发器：DML触发器是当数据库服务器中发生数据操作语言（Data Manipulation Language）事件时执行的存储过程。DML触发器又分为两类：After触发器和Instead Of触发器

  DDL触发器：DDL触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程。DDL触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。

 DML触发器的分类

SQL Server 2005的DML触发器分为两类：

  After触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用Rollback Transaction语句来回滚本次的操作。

 

 Instead Of触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作

 

create table employees(

    employee_id int primary key not null,

    last_name varchar(50) null,

    first_name varchar(50) null,

    hire_date datetime null,

    job_id int null,

    email varchar(500) null,

    department_id int null,

    salary int null,

    commission_pct int null

)

 

create trigger employees_update

 on employees

   after update

 as

 begin

         print '有一个员工更改了'

 end

 

create trigger employees_insert

 on employees

   after insert

 as

 begin

         print '有一个员工增加了'

 end

 

create trigger employees_delete

 on employees

   after delete

 as

 begin

         print '有一个员工删除了'

 end

 

 

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 12345,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

 

delete employees

 

update employees set last_name = 'dabing' where employee_id=12345;

 

select * from employees

select commission_pct from employees where employee_id=12345;

 

 

 

 

drop trigger employees_insert

create trigger employees_insert

  on employees

  after insert

as

begin

  declare @employee_id int

  set @employee_id = (select employee_id from inserted)

  if(@employee_id > 5 )

  begin

  print '员工号大于的不能添加'

  Rollback Transaction

  end

end;

 

alter trigger employees_insert

  on employees

  instead of insert

as

begin

  declare @employee_id int

  set @employee_id = (select employee_id from inserted)

  if(@employee_id > 5 )

  begin

  print '员工号大于的不能添加'

  end

end;

 

 

 

 

drop trigger employees_delete

create trigger employees_delete

  on employees

  instead of delete

as

begin

  declare @employee_id int                  

  set @employee_id = (select employee_id from deleted)

  if(@employee_id > 2 )

  begin

  print '员工号大于的不能删除'

  end

end;

 

delete employees where employee_id = '3'

 

 

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 1,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 2,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 3,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 4,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 5,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )

values( 6,'Chen','Donny', '2010-05-08', 12, 'donny@hotmail.com',60,10000,25);

select * from employees