為什麽批量更新以後，觸發器只影響了一條數據？
ALTER TRIGGER [dbo].[reqLocalCadreUpdate] ON [dbo].[reqLocalCadre] AFTER UPDATE
AS
BEGIN
Declare @RequireID bigint,
@RequireCode varchar(50),
@BUCode varchar(50),
@DeptID varchar(50),
@PositionSerialCode varchar(20),
@PostCode varchar(20),
@GradeCodeMin varchar(20),
@GradeCodeMax varchar(20),
@RequireNumber int,
@IsInProjectbit,
@ApproveCompleteTimedatetime,
@RequireID_old bigint,
@RequireCode_old varchar(50),
@BUCode_old varchar(50),
@DeptID_old varchar(50),
@PositionSerialCode_old varchar(20),
@PostCode_old varchar(20),
@GradeCodeMin_old varchar(20),
@GradeCodeMax_old varchar(20),
@RequireNumber_old int,
@IsInProject_oldbit,
@ApproveCompleteTime_olddatetime
select @RequireID=RequireID,@RequireCode=RequireCode,@BUCode=BUCode,@DeptID=DeptID,@PositionSerialCode=PositionSerialCode,@PostCode=PostCode,@GradeCodeMin=GradeCodeMin,@GradeCodeMax=GradeCodeMax,@RequireNumber=RequireNumber, @IsInProject=IsInProject, @ApproveCompleteTime=ApproveCompleteTime from inserted
select @RequireID_old=RequireID,@RequireCode_old=RequireCode,@BUCode_old=BUCode,@DeptID_old=DeptID,@PositionSerialCode_old=PositionSerialCode,@PostCode_old=PostCode,@GradeCodeMin_old=GradeCodeMin,@GradeCodeMax_old=GradeCodeMax,@RequireNumber_old=RequireNumber, @IsInProject_old=IsInProject, @ApproveCompleteTime_old=ApproveCompleteTime from deleted
--RequireCode
if @RequireCode!=@RequireCode_old
begin
select @RequireCode=RequireCode from inserted
update schRecruitPlan set RequireCode=@RequireCode where RequireID=@RequireID and HasDeleted=0
update pubJobAnnounce set RequireCode=@RequireCode where RequireID=@RequireID
print 'RequireCode updated! '
end
--BUCode
if @BUCode!=@BUCode_old
begin
select @BUCode=BUCode from inserted
update schRecruitPlan set BUCode=@BUCode where RequireID=@RequireID and HasDeleted=0
update pubJobAnnounce set BUCode=@BUCode where RequireID=@RequireID
print 'BUCode updated! '
end
--DeptID
if @DeptID!=@DeptID_old
begin
select @DeptID=DeptID from inserted
update schRecruitPlan set DeptID=@DeptID where RequireID=@RequireID and HasDeleted=0
update pubJobAnnounce set DeptID=@DeptID where RequireID=@RequireID
print 'DeptID updated! '
end
--PositionSerialCode
if @PositionSerialCode!=@PositionSerialCode_old
begin
select @PositionSerialCode=PositionSerialCode from inserted
update schRecruitPlan set PositionSerialCode=@PositionSerialCode where RequireID=@RequireID and HasDeleted=0
update pubJobAnnounce set PositionSerialCode=@PositionSerialCode where RequireID=@RequireID
print 'PS code updated! '
end
--PostCode
if @PostCode!=@PostCode_old
begin
select @PostCode=PostCode from inserted
update schRecruitPlan set PostCode=@PostCode where RequireID=@RequireID and HasDeleted=0
update pubJobAnnounce set PostCode=@PostCode where RequireID=@RequireID
print 'post code updated! '
end
--GradeCodeMin
if @GradeCodeMin!=@GradeCodeMin_old
begin
select @GradeCodeMin=GradeCodeMin from inserted
update schRecruitPlan set GradeCodeMin=@GradeCodeMin where RequireID=@RequireID and HasDeleted=0
update pubJobAnnounce set GradeCodeMin=@GradeCodeMin where RequireID=@RequireID
print 'grade code min updated! '
end
--GradeCodeMax
if @GradeCodeMax!=@GradeCodeMax_old
begin
select @GradeCodeMax=GradeCodeMax from inserted
update schRecruitPlan set GradeCodeMax=@GradeCodeMax where RequireID=@RequireID and HasDeleted=0
update pubJobAnnounce set GradeCodeMax=@GradeCodeMax where RequireID=@RequireID
print 'grade code max updated! '
end
--RequireNumber
if @RequireNumber!=@RequireNumber_old
begin
select @RequireNumber=RequireNumber from inserted
update schRecruitPlan set RequireNumber=@RequireNumber where RequireID=@RequireID and HasDeleted=0
update pubJobAnnounce set RequireNumber=@RequireNumber where RequireID=@RequireID
print 'RequireNumber updated! '
end
--TrackingUser
select @IsInProject=IsInProject from reqLocalCadre where RequireID=@RequireID
if @BUCode!=@BUCode_old or @PositionSerialCode!=@PositionSerialCode_old or @IsInProject!=@IsInProject_old
begin
if @IsInProject=1--專案跟進
begin
update schRecruitPlan set TrackingUserID=b.UserID, TrackingUserName=b.UserName from
(
SELECT TOP 1 A.UserID, A.ChineseName AS UserName, A.ExPhone from sysUser A (nolock)
INNER JOIN basCommissioner B (nolock) on B.UserID=A.UserID
WHERE A.UserID IN (SELECT UserID FROM sysUserRecruitOrg (nolock) WHERE OrgID= 'PRJ ')
AND B.PSCode=@PositionSerialCode AND B.BUCode=@BUCode
) as b
where RequireID=@RequireID and HasDeleted=0
end
else
begin
update schRecruitPlan set TrackingUserID=b.UserID, TrackingUserName=b.UserName from
(
SELECT TOP 1 A.UserID, A.ChineseName AS UserName, A.ExPhone from sysUser A (nolock)
INNER JOIN basCommissioner B (nolock) on B.UserID=A.UserID
WHERE A.UserID NOT IN (SELECT UserID FROM sysUserRecruitOrg (nolock) WHERE OrgID= 'PRJ ')
AND B.PSCode=@PositionSerialCode AND B.BUCode=@BUCode
) as b
where RequireID=@RequireID and HasDeleted=0
end
--職位發佈
if @IsInProject=1--專案跟進
begin
update pubJobAnnounce set TrackingUserID=b.UserID, TrackingUserName=b.UserName, Tel=b.phone, Email=b.Email from
(SELECT TOP 1 A.UserID, A.ChineseName AS UserName, A.ExPhone as phone, A.Email from sysUser A (nolock)
INNER JOIN basCommissioner B (nolock) on B.UserID=A.UserID
WHERE A.UserID IN (SELECT UserID FROM sysUserRecruitOrg (nolock) WHERE OrgID= 'PRJ ')
AND B.PSCode=@PositionSerialCode AND B.BUCode=@BUCode
) as b
where RequireID=@RequireID
end
else
begin
update pubJobAnnounce set TrackingUserID=b.UserID, TrackingUserName=b.UserName, Tel=b.phone, Email=b.Email from
(SELECT TOP 1 A.UserID, A.ChineseName AS UserName, A.ExPhone as phone, A.Email from sysUser A (nolock)
INNER JOIN basCommissioner B (nolock) on B.UserID=A.UserID
WHERE A.UserID NOT IN (SELECT UserID FROM sysUserRecruitOrg (nolock) WHERE OrgID= 'PRJ ')
AND B.PSCode=@PositionSerialCode AND B.BUCode=@BUCode
) as b
where RequireID=@RequireID
end
print 'TrackingUser updated! '
end
-- 更新簽核完成時間到招募計劃的修改日期
if @ApproveCompleteTime_old is null and @ApproveCompleteTime is not null or @ApproveCompleteTime!=@ApproveCompleteTime_old
begin
update schRecruitPlan set ModifyDate=@ApproveCompleteTime where RequireID=@RequireID and HasDeleted=0
print 'ApproveCompleteTime updated! '
end
END
[解决办法]
AFTER UPDATE,在更新以后操作，详见联机说明
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
[解决办法]
一个批量操作的SQL语句，触发一次触发器，而触发器的inserted/deleted表中存在多条记录。
如果你的业务逻辑处理起来比较复杂，可以在触发器中用游标遍历inserted/deleted的方式逐个处理。
[解决办法]
觸發器寫的有問題，
select @RequireID=RequireID,@RequireCode=RequireCode,@BUCode=BUCode,@DeptID=DeptID,@PositionSerialCode=PositionSerialCode,@PostCode=PostCode,@GradeCodeMin=GradeCodeMin,@GradeCodeMax=GradeCodeMax,@RequireNumber=RequireNumber, @IsInProject=IsInProject, @ApproveCompleteTime=ApproveCompleteTime from inserted
select @RequireID_old=RequireID,@RequireCode_old=RequireCode,@BUCode_old=BUCode,@DeptID_old=DeptID,@PositionSerialCode_old=PositionSerialCode,@PostCode_old=PostCode,@GradeCodeMin_old=GradeCodeMin,@GradeCodeMax_old=GradeCodeMax,@RequireNumber_old=RequireNumber, @IsInProject_old=IsInProject, @ApproveCompleteTime_old=ApproveCompleteTime from deleted
你這麼寫，批量更新的時候，就只能得到一條紀錄的值，那麼“觸發器只影響了一條數據”。
如果你的邏輯複雜的話，用游標逐條讀出inserted和inserted的數據，然後一條條做處理。
[解决办法]
是你写的问题，这种情况应该用游标遍历inserted或者deleted表，许多人都犯这个错误。
[解决办法]
真够复杂的
oracle里有行级触发器，sqlserver里没有，只好使用游标遍历幻表
[解决办法]
通常情況下，關鍵字是不會做更改的。
你就用關鍵字那個字段將兩表的數據關聯起來做對比。
[解决办法]
insert时,inserted有行,deleted没有行
delete时,deleted有行,inserted没有行
也可以分别建insert和delete触发器
create trigger ti_表 on 表
for insert
as
...
create trigger td_表 on 表
for delete
as
...
[解决办法]
了解。不過inserted和deleted表，如何對應作對比比較是否有更改呢?
---------------------------
判断RequireCode是否被更改
where exists(select 1 from inserted i,deleted d where i.RequireID=d.RequireID and isnull(i.RequireCode, ' ') <> isnull(d.RequireCode, ' '))
