更换经理时的触发器.sql

来自「关于数据库的 资料」· SQL 代码 · 共 40 行

create trigger emp_title
on employee
instead of update
as
if update(title)
begin
  
   if (select inserted.title from inserted)='经理'
    begin
      declare @dept varchar(20),@name varchar(20)
      --set @dept=(select deleted.deptID from deleted)
      select @dept=deleted.deptID from deleted
      set @name=(select deleted.EmpName from deleted)

      update employee
      set title='职员'
      where deptID=@dept and title='经理'

      update employee
      set title='经理'
      where EmpID=(select deleted.empID from deleted)

      update deptment
      set  deptmanger=@name
      where deptID=@dept
    end
  else
     begin
        update employee
        set title=(select inserted.title from inserted)
        where empID=(select deleted.empID from deleted)
    end
end

select * from employee
select * from deptment

update employee
set title='经理'
where empID='00001'