无法执行触发器更新操作解决方案
日期：2014-05-18 浏览次数：20518 次
无法执行触发器更新操作
USE [DB_AutoServiceCenter]
GO
/****** Object: Trigger [dbo].[User_Update] Script Date: 10/25/2011 13:37:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[User_Update]
ON [dbo].[HR_User]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @LoginName varchar(30)
SELECT @LoginName=LoginName FROM deleted
if Update(UserFlag)
DELETE CallCenter.dbo.Employee WHERE EmployeeNo=@LoginName
if Update(LoginName) or Update(UserName) or Update(LoginPwd) or Update(ChannelNo)
Update Employee
Set EmployeeNo=deleted.LoginName,EmployeeName=deleted.UserName,Password=deleted.LoginPwd,ChannelNo=deleted.ChannelNo FROM CallCenter.dbo.Employee,deleted
WHERE EmployeeNo='@LoginName'
END
上面是代码，第一个更新是没问题，但是不知道为什么第二个更新操作不管怎样都不执行？请大家帮忙看看
------解决方案--------------------
跨数据库的触发器.....
SELECT @LoginName=LoginName FROM deleted
只能获得一条记录的loginname值,要改成:
USE [DB_AutoServiceCenter]
GO
/****** Object: Trigger [dbo].[User_Update] Script Date: 10/25/2011 13:37:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[User_Update]
ON [dbo].[HR_User]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @LoginName varchar(30)
SELECT @LoginName=LoginName FROM deleted
if Update(UserFlag)
DELETE CallCenter.dbo.Employee WHERE EmployeeNo=@LoginName
if Update(LoginName) or Update(UserName) or Update(LoginPwd) or Update(ChannelNo)
Update Employee
Set EmployeeNo=deleted.LoginName,EmployeeName=deleted.UserName,Password=deleted.LoginPwd,ChannelNo=deleted.ChannelNo FROM CallCenter.dbo.Employee,deleted
WHERE EmployeeNo='@LoginName'
END
上面是代码，第一个更新是没问题，但是不知道为什么第二个更新操作不管怎样都不执行？请大家帮忙看看
------解决方案--------------------
跨数据库的触发器.....
SELECT @LoginName=LoginName FROM deleted
只能获得一条记录的loginname值,要改成:
- SQL code
Update Employee Set EmployeeNo=deleted.LoginName,EmployeeName=deleted.UserName,Password=deleted.LoginPwd,ChannelNo=deleted.ChannelNo FROM CallCenter.dbo.Employee,deleted WHERE Employee.EmployeeNo=deleted.LoginName
------解决方案--------------------
- SQL code
--两表关联字段自己补上 Update Employee Set EmployeeNo=deleted.LoginName,EmployeeName=deleted.UserName,Password=deleted.LoginPwd,ChannelNo=deleted.ChannelNo FROM CallCenter.dbo.Employee a join deleted b on (a.关联字段=b.关联字段) WHERE EmployeeNo='@LoginName'
------解决方案--------------------
- SQL code
--应该这样更新... Update a Set EmployeeNo=b.LoginName,EmployeeName=b.UserName,Password=b.LoginPwd,ChannelNo=b.ChannelNo FROM CallCenter.dbo.Employee a join deleted b on (a.关联字段=b.关联字段) WHERE a.EmployeeNo=@LoginName
------解决方案--------------------
- SQL code
--确定这是关联条件的话用下面试试，然后update触发下，看看有没走到1，然后有没走到2？
print 1
Update a Set EmployeeName=b.UserName,Password=b.LoginPwd,ChannelNo=b.ChannelNo
FROM CallCenter.dbo.Employee a join deleted b on (a.EmployeeNo=b.LoginName)
WHERE a.EmployeeNo=@LoginName
print 2
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
