试一下下面的sqlSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER InsertDept
ON EisoftHuman.dbo.Dept
AFTER INSERT,UPDATE
AS
BEGIN
IF NOT EXISTS(SELECT A.DeptAutoID FROM EisoftHumanSec.dbo.Dept A INNER JOIN INSERTED B ON A.DeptAutoID=B.DeptAutoID)
begin
INSERT INTO EisoftHumanSec.dbo.Dept
( Creation ,
Created_by ,
DeptID ,
DeptName ,
CaseID ,
Memo ,
DeptFlag ,
Manager ,
DeptTypeID ,
stWPID ,
MakeLineCode ,
DeptLevel ,
EmpIDHead ,
MaxEmpID ,
CardColor ,
LeaveDay
)
SELECT Creation ,
Created_by ,
DeptID ,
DeptName ,
CaseID ,
Memo ,
DeptFlag ,
Manager ,
DeptTypeID ,
stWPID ,
MakeLineCode ,
DeptLevel ,
EmpIDHead ,
MaxEmpID ,
CardColor ,
LeaveDay
FROM INSERTED B
WHERE DeptAutoID=B.DeptAutoID
END
ELSE BEGIN
UPDATE A SET
A.Creation=B.Creation,
A.Created_by=B.Created_by,
A.DeptID=B.DeptID,
A.DeptName=B.DeptName,
A.CaseID=B.CaseID,
A.Memo=B.Memo,
A.DeptFlag=B.DeptFlag,
A.Manager=B.Manager,
A.DeptTypeID=B.DeptTypeID,
A.stWPID=B.stWPID,
A.MakeLineCode=B.MakeLineCode,
A.DeptLevel=B.DeptLevel,
A.EmpIDHead=B.EmpIDHead,
A.MaxEmpID=B.MaxEmpID,
A.CardColor=B.CardColor,
A.LeaveDay=B.LeaveDay
FROM INSERTED B INNER JOIN EisoftHumanSec.dbo.Dept A
ON A.DeptAutoID=B.DeptAutoID
END
end
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER InsertDept
ON EisoftHuman.dbo.Dept
AFTER INSERT,UPDATE
AS
BEGIN
IF NOT EXISTS(SELECT A.DeptAutoID FROM EisoftHumanSec.dbo.Dept A INNER JOIN INSERTED B ON A.DeptAutoID=B.DeptAutoID)
begin
INSERT INTO EisoftHumanSec.dbo.Dept
( Creation ,
Created_by ,
DeptID ,
DeptName ,
CaseID ,
Memo ,
DeptFlag ,
Manager ,
DeptTypeID ,
stWPID ,
MakeLineCode ,
DeptLevel ,
EmpIDHead ,
MaxEmpID ,
CardColor ,
LeaveDay
)
SELECT Creation ,
Created_by ,
DeptID ,
DeptName ,
CaseID ,
Memo ,
DeptFlag ,
Manager ,
DeptTypeID ,
stWPID ,
MakeLineCode ,
DeptLevel ,
EmpIDHead ,
MaxEmpID ,
CardColor ,
LeaveDay
FROM INSERTED B
WHERE DeptAutoID=B.DeptAutoID
END
ELSE BEGIN
UPDATE A SET
A.Creation=B.Creation,
A.Created_by=B.Created_by,
A.DeptID=B.DeptID,
A.DeptName=B.DeptName,
A.CaseID=B.CaseID,
A.Memo=B.Memo,
A.DeptFlag=B.DeptFlag,
A.Manager=B.Manager,
A.DeptTypeID=B.DeptTypeID,
A.stWPID=B.stWPID,
A.MakeLineCode=B.MakeLineCode,
A.DeptLevel=B.DeptLevel,
A.EmpIDHead=B.EmpIDHead,
A.MaxEmpID=B.MaxEmpID,
A.CardColor=B.CardColor,
A.LeaveDay=B.LeaveDay
FROM INSERTED B INNER JOIN EisoftHumanSec.dbo.Dept A
ON A.DeptAutoID=B.DeptAutoID
END
end
帮解释一下,谢谢
你的 if 和 else 都应该有一对单独的 begin 和 end ,如果你的 if 或 else 中只有一条sql 语句,这对 begin 和 end 可以省掉,一般情况下不建议省略。