sqlserver2000触发器怎么if多少参数,实时同步? 20
当人事表EMPLOYEE表中发现4种情况(A新增人员资料、B修改人员资料、C调离职、D当USE表数据为空或第一次,需要重新从EMPLOYEE中全部导入过来),想写一个触发...
当人事表EMPLOYEE 表中发现4种情况(A新增人员资料、B修改人员资料、C调离职、D当USE表数据为空或第一次,需要重新从EMPLOYEE中全部导入过来),想写一个触发器如何实时同步到USE表中?
请问怎么修改更完美?
create trigger emp_udpate on employee
after INSERT,UPDATE,DELETE
as
declare @state int,@i int
set @state=(select top 1 state from inserted)
delete from USE where isnull(name,'')='' --先删除USE表中无姓名
if (@state=9)--离职情况
begin
delete a from USE a join EMPLOYEE e on a.name=e.name and a.empid=e.ID where e.lzdate<dateadd(dd,-10,GETDATE())--离职10后删除
end
if (@state=0)--在职情况
begin
set @i=(select count(userid) from USE where empid in (select id from inserted) or ssn in (select code from inserted))
if (@i>0)
begin
update U set U.cardno=E.cardno,U.NAME=E.NAME,U.SSN=E.CODE,U.DEPTCODE=E.DEPT
FROM USE U join EMPLOYEE E on U.SSN=E.Code and E.ID=U.empid
WHERE U.CARDNO<>E.CARDNO OR U.NAME<>E.NAME OR U.SSN<>E.CODE OR U.DEPTCODE<>E.DEPT
end
if (@i=0)--新进情况
begin
insert into USE(BADGENUMBER,NAME,verificationmethod,DEFAULTDEPTID,cardno,Empid,SSN,DEPTCODE,GENDER)
select cast(isnull(BADGENUMBER,NAME,verificationmethod,DEFAULTDEPTID,cardno,Empid,SSN,DEPTCODE,GENDER
from inserted E
join DEPARTMENTS D on E.Dept=D.DeptCode
where cast(E.cardno as int) not in (select isnull(BADGENUMBER,'') from USE) and e.state=0
end
end
想用一个触发器不是多个的,而且数据量也很大的 展开
请问怎么修改更完美?
create trigger emp_udpate on employee
after INSERT,UPDATE,DELETE
as
declare @state int,@i int
set @state=(select top 1 state from inserted)
delete from USE where isnull(name,'')='' --先删除USE表中无姓名
if (@state=9)--离职情况
begin
delete a from USE a join EMPLOYEE e on a.name=e.name and a.empid=e.ID where e.lzdate<dateadd(dd,-10,GETDATE())--离职10后删除
end
if (@state=0)--在职情况
begin
set @i=(select count(userid) from USE where empid in (select id from inserted) or ssn in (select code from inserted))
if (@i>0)
begin
update U set U.cardno=E.cardno,U.NAME=E.NAME,U.SSN=E.CODE,U.DEPTCODE=E.DEPT
FROM USE U join EMPLOYEE E on U.SSN=E.Code and E.ID=U.empid
WHERE U.CARDNO<>E.CARDNO OR U.NAME<>E.NAME OR U.SSN<>E.CODE OR U.DEPTCODE<>E.DEPT
end
if (@i=0)--新进情况
begin
insert into USE(BADGENUMBER,NAME,verificationmethod,DEFAULTDEPTID,cardno,Empid,SSN,DEPTCODE,GENDER)
select cast(isnull(BADGENUMBER,NAME,verificationmethod,DEFAULTDEPTID,cardno,Empid,SSN,DEPTCODE,GENDER
from inserted E
join DEPARTMENTS D on E.Dept=D.DeptCode
where cast(E.cardno as int) not in (select isnull(BADGENUMBER,'') from USE) and e.state=0
end
end
想用一个触发器不是多个的,而且数据量也很大的 展开
1个回答
展开全部
设计不合理啊,添加、修改、删除 写同一触发器里,其中一种触发时,另外两种也会执行到,特别是你还强调了数据量会很大,这样效率很低的。
离职超10天删除 这种情况放在这触发器处理不合适,如果修改为离职后的11天内刚好没再 添加 或修改 或删除 过记录,这些记录不就会一直留着,直到再次触发这触发器,这样也可以?
在职情况 的UPDATE,不能直接跟 employee 关联,应该跟 Inserted 表(如果UPDATE 写单独触发器,可以通过 UPDATE(字段名) 来判断是否有修改到这字段)
看代码,是直接拿变量来存放 state 的值,这是每次都只会操作一条记录,不会批量操作?如果是的话,不如在最先查 state 时,直接把其他可能用到的字段,同时用变量起来,下面的操作都用这些变量就好,没必要再去表关联。判断也改成 if...else...,如
IF @state =9 /* 离职情况 */
BEGIN
XXXX
END
ELSE IF @state =0 /* 在职情况 */
BEGIN
IF NOT EXISTS(SELECT TOP 1 1 FROM [USE] WHERE empid =@id OR ssn =@code) /* 新进情况 */
BEGIN
XXXX
END
ELSE
BEGIN
XXXX
END
END
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询