触发存储错误信息 [英] Trigger Storing wrong information

问题描述

Hello Everyone，



以下是我在SQL Server 2008上的触发器

  ALTER   TRIGGER  [dbo]。[Trigger_UpdateLeadMasters]  ON  [dbo]。[LeadMasters] 
  FOR  更新 
  AS  
 
  DECLARE   @ CompanyName   nvarchar （ 50 ）; 
  DECLARE   @ PersonName   nvarchar  （ 50 ）; 
  DECLARE   @ Designation   nvarchar  （ 50 ）; 
  DECLARE   @ Number   nvarchar  （ 50 ）; 
  DECLARE   @ Number2   nvarchar  （ 50 ）; 
  DECLARE   @ Emailaddress   nvarchar  （ 50 ）; 
  DECLARE   @ Address   nvarchar  （MAX）; 
  DECLARE   @ Address2   nvarchar  （MAX）; 
  DECLARE   @ CityName   nvarchar  （ 50 ）; 
  DECLARE   @ State   nvarchar  （ 50 ）; 
  DECLARE   @ PinNumber   nvarchar  （ 50 ）; 
  DECLARE   @ Product   nvarchar  （ 50 ）; 
  DECLARE   @ RemarkNote   nvarchar  （MAX）; 
  DECLARE   @ Audit_Action   varchar  （ 100 ）; 
 
 
 选择 @ CompanyName = i.CompanyName 从插入i ; 
 选择 @ PersonName = i.PersonName 从插入i; 
 选择 @ Designation = i.Designation 从插入i; 
 选择 @ Number = i.Number 从插入i; 
 选择 @ Number2 = i.Number2 从插入i; 
 选择 @ EmailAddress = i.EmailAddress 来自插入i; 
 选择 @地址= i.Address 来自插入i; 
 选择 @ Address2 = i.Address2 从插入i; 
 选择 @ CityName = i.CityName 从插入i; 
 选择 @ State = i.State 从插入i; 
 选择 @PinNumber = i.PinNumber 来自插入i; 
 选择 @ Product = i.Product 从插入i; 
 选择 @RemarkNote = i.RemarkNote 来自插入i; 
 
  IF  更新（CompanyName）
  set  @ Audit_Action = ' 名称已修改或更新' 
  IF   UPDATE （PersonName）
  set  @ Audit_Action = ' 人名修改或更新' 
  IF  更新（指定）
  set  @ Audit_Action = ' 名称已修改或更新' 
  IF  更新（数字）
  set  @ Audit_Action = ' 第一个电话号码已修改或更新ed' 
  IF  更新（Number2）
  set  @ Audit_Action = ' 第二个电话号码已修改或更新' 
  IF  更新（EmailAddress）
  set  @ Audit_Action = ' 电子邮件地址已修改或更新' 
 < span class =code-keyword> IF  更新（地址）
  set  @ Audit_Action = ' 地址已修改或更新' 
  IF  更新（地址2）
 设置 @ Audit_Action = ' 备用地址Modifi ed或Updated' 
  IF  更新（CityName）
 < span class =code-keyword> set  @ Audit_Action = ' 城市名称已修改或更新' 
  IF  更新（州）
 设置 @ Audit_Action = ' 状态修改或更新' 
 < span class =code-keyword> IF   UPDATE （PinNumber）
  set  @ Audit_Action = '  PinNumber已修改或更新' 
  IF  更新（产品）
  set  @ Audit_Action = ' 产品字段已修改或更新ed' 
  IF   UPDATE （RemarkNote）
  set  @ Audit_Action = ' 备注注释已修改或更新' 
 
 插入 进入 LoggerLeadMasters（CompanyName，PersonName，Designation，Number，Number2， EmailAddress，Address，Address2，CityName，State，PinNumber，Product，RemarkNote，Audit_Action，Audit_Timestamp）
 值（ @CompanyName ， @ PersonName ， @ Designation ， @Number ， @ Number2 ， @ EmailAddress ， @Address ， @ Address2 ， @ CityName ， @ State ， @ PinNumber ， @ Product ， @ RemarkNote ， @ Audit_Action ，GETDATE（））

但问题是每当我修改LeadMasters表中的任何字段时，触发器就行了



< pre lang =SQL> IF UPDATE （RemarkNote）
set @ Audit_Action = ' 备注注意修改或更新'

并将错误信息存储到LoggerLeadMasters。任何人都可以帮我解决这个问题



谢谢

解决方案

如果它只用于单行更新

也许这样的事情

刚刚在记事本中完成，没有检查等。



ALTER TRIGGER [dbo]。[Trigger_UpdateLeadMasters] ON [dbo]。[LeadMasters]

FOR UPDATE

AS



IF EXISTS（

SELECT'名称已修改或更新'

FROM FROM i JOIN已删除d ON（/ *由UpdateLeadMasters的PK加入* /）

WHERE（i.CompanyName IS NULL且d.CompanyName IS NOT NULL）

OR（i.CompanyName IS NOT NULL且d.CompanyName IS NULL）

或（i.CompanyName IS NOT NULL且d.CompanyName IS NOT NULL

AND i.CompanyName<> d.CompanyName）

）

SET @AuditAction ='名称已修改或更新'



如果存在（

SELECT'人名被修改或更新'

FROM FROM i JOIN删除d ON（/ *由UpdateLeadMasters的PK加入* /）

WHERE（i.PersonName为空且d.PersonName不为空）

OR（i.PersonName IS非空且d.PersonName为空）

OR（i。 PersonName不是NULL而且d.PersonName不是NULL

AND i.PersonName<> d.PersonName）

）

SET @ AuditAction ='人名被修改或更新'



/ * ...等.. * /



插入LoggerLeadMasters（CompanyName，PersonName，Designation，Number，Number2，EmailAddress，Address，Address2，CityName，State，PinNumber，Product，RemarkNote，Audit_Action，Audit_Timestamp）

值（@ CompanyName，@ PersonName @名称，@号，@ NUMBER2，@ EmailAddress的，@地址@地址2，@ CITYNAME，@国@ PinNumber，@表示P roduct，@ RemarkNote，@ Audit_Action，GETDATE（））

显然后面的Insert需要相应修改

Hello Everyone,

Following is my trigger on SQL Server 2008

ALTER TRIGGER [dbo].[Trigger_UpdateLeadMasters] ON [dbo].[LeadMasters]
FOR UPDATE
AS

DECLARE @CompanyName	nvarchar(50);	
DECLARE @PersonName	nvarchar(50);	
DECLARE @Designation	nvarchar(50);	
DECLARE @Number	nvarchar(50);	
DECLARE @Number2	nvarchar(50);	
DECLARE @Emailaddress	nvarchar(50);	
DECLARE @Address	nvarchar(MAX);	
DECLARE @Address2	nvarchar(MAX);	
DECLARE @CityName	nvarchar(50);	
DECLARE @State	nvarchar(50);	
DECLARE @PinNumber	nvarchar(50);	
DECLARE @Product	nvarchar(50);	
DECLARE @RemarkNote	nvarchar(MAX);
DECLARE @Audit_Action varchar(100);


select @CompanyName=i.CompanyName from inserted i;	
select @PersonName=i.PersonName from inserted i;		
select @Designation=i.Designation from inserted i;
select @Number=i.Number from inserted i;		
select @Number2=i.Number2 from inserted i;		
select @EmailAddress=i.EmailAddress from inserted i;		
select @Address=i.Address from inserted i;		
select @Address2=i.Address2 from inserted i;	
select @CityName=i.CityName from inserted i;		
select @State=i.State from inserted i;		
select @PinNumber=i.PinNumber from inserted i;	
select @Product=i.Product from inserted i;		
select @RemarkNote=i.RemarkNote from inserted i;

IF UPDATE(CompanyName)
set @Audit_Action=' Name Modified or Updated'
IF UPDATE(PersonName)
set @Audit_Action='Person Name Modified or Updated'
IF UPDATE(Designation)
set @Audit_Action='Designation Modified or Updated'
IF UPDATE(Number)
set @Audit_Action='1st Phone Number Modified or Updated'
IF UPDATE(Number2)
set @Audit_Action='2nd Phone Number Modified or Updated'
IF UPDATE(EmailAddress)
set @Audit_Action='Email Address Modified or Updated'
IF UPDATE(Address)
set @Audit_Action='Address Modified or Updated'
IF UPDATE(Address2)
set @Audit_Action='Alternate Address Modified or Updated'
IF UPDATE(CityName)
set @Audit_Action='City Name Modified or Updated'
IF UPDATE(State)
set @Audit_Action='State Modified or Updated'
IF UPDATE(PinNumber)
set @Audit_Action='PinNumber Modified or Updated'
IF UPDATE(Product)
set @Audit_Action='Product Field Modified or Updated'
IF UPDATE(RemarkNote)
set @Audit_Action='Remark Note Modified or Updated'

insert into LoggerLeadMasters(CompanyName,PersonName,Designation,Number,Number2,EmailAddress,Address,Address2,CityName,State,PinNumber,Product,RemarkNote,Audit_Action,Audit_Timestamp)
Values(@CompanyName,@PersonName,@Designation,@Number,@Number2,@EmailAddress,@Address,@Address2,@CityName,@State,@PinNumber,@Product,@RemarkNote,@Audit_Action,GETDATE())

But the problem is whenever I modify any field in LeadMasters Table, the trigger goes to line

IF UPDATE(RemarkNote)
set @Audit_Action='Remark Note Modified or Updated'

and stores wrong information to LoggerLeadMasters.Can anyone help me to fix this issue

Thanks

解决方案

If it is only ever for single-row updates
Maybe something like this
Just done in notepad, not checked etc.

ALTER TRIGGER [dbo].[Trigger_UpdateLeadMasters] ON [dbo].[LeadMasters]
FOR UPDATE
AS

IF EXISTS(
SELECT 'Name Modified or Updated'
FROM inserted i JOIN deleted d ON (/* join by PK of UpdateLeadMasters */)
WHERE (i.CompanyName IS NULL AND d.CompanyName IS NOT NULL)
OR (i.CompanyName IS NOT NULL AND d.CompanyName IS NULL)
OR (i.CompanyName IS NOT NULL AND d.CompanyName IS NOT NULL
AND i.CompanyName <> d.CompanyName)
)
SET @AuditAction = 'Name Modified or Updated'

IF EXISTS(
SELECT 'Person Name Modified or Updated'
FROM inserted i JOIN deleted d ON (/* join by PK of UpdateLeadMasters */)
WHERE (i.PersonName IS NULL AND d.PersonName IS NOT NULL)
OR (i.PersonName IS NOT NULL AND d.PersonName IS NULL)
OR (i.PersonName IS NOT NULL AND d.PersonName IS NOT NULL
AND i.PersonName <> d.PersonName)
)
SET @AuditAction = 'Person Name Modified or Updated'

/* ... etc ..*/

insert into LoggerLeadMasters(CompanyName,PersonName,Designation,Number,Number2,EmailAddress,Address,Address2,CityName,State,PinNumber,Product,RemarkNote,Audit_Action,Audit_Timestamp)
Values(@CompanyName,@PersonName,@Designation,@Number,@Number2,@EmailAddress,@Address,@Address2,@CityName,@State,@PinNumber,@Product,@RemarkNote,@Audit_Action,GETDATE())

Obviously the trailing Insert would need to modified accordingly

这篇关于触发存储错误信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！