Create trigger tr_STUDENT on STUDENT
for insert,update,delete
as

 --insert
 IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
 begin 
  insert into USERID
  select SNO,SNO,'学生' from inserted
 end
 
 --update
 IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
 BEGIN
       declare @DeleteID int
    declare @InsertID int
    select @DeleteID=SNO from deleted
    select @InsertID=SNO from inserted
    update USERID set ID=@InsertID,PASSWORD=@InsertID where ID=@DeleteID

 end
  
  --delete
  IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
  BEGIN
   delete A from USERID A,deleted B where A.ID=B.SNO
  end

------解决方案--------------------
官方文档 http://msdn.microsoft.com/zh-cn/library/ms189799.aspx

------解决方案--------------------
http://tech.ccidnet.com/zt/chufaqi/

------解决方案--------------------
触发器综述
http://topic.csdn.net/u/20081005/11/57061a18-c234-40ee-ba4b-1f4c3bc7f09a.html?13366