create trigger emp_title
on employee
instead of update
as
if update(title)
begin
  
   if (select inserted.title from inserted)='經(jīng)理'
    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='經(jīng)理'

      update employee
      set title='經(jīng)理'
      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='經(jīng)理'
where empID='00001'