question

KaiYang-6525 avatar image
0 Votes"
KaiYang-6525 asked ErlandSommarskog commented

find correct column names from inserted and deleted tables

Hello all,
I adapt a trigger. there is a part of code below:

SELECT INTO #ins FROM inserted
SELECT
INTO #del FROM deleted

select @UserName = EMP_ModifiedBy, @empcode = emp_cd from #ins
if isnull(@UserName,'') = ''
select @UserName = EMP_ModifiedBy, @empcode = emp_cd from #del

when I run the trigger, the error message are:

Msg 207, Level 16, State 1, Procedure TR_OnStudy_OS, Line 38 [Batch Start Line 0]
Invalid column name 'EMP_ModifiedBy'.
Msg 207, Level 16, State 1, Procedure TR_OnStudy_OS, Line 38 [Batch Start Line 0]
Invalid column name 'emp_cd'.

Can you help me to fix the problem?
Thanks,
Kai

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

First of all, reading values into to variables from these tables is not a fantastic idea. Keep in mind that a statement may affect multiple rows, and trigger fires once per statement. So if you have many rows, why only work with one.

The simple explanation for the error is that the target table does not have any columns called emp_cd or EMP_ModifiedBy. So check spelling, include upper/lowercase in case you have a case-sensitive collation.

There is a second possible explanation, if you have nested triggers, and the outer trigger also has these #ins and #del tables. There are some interesting effects in SQL Server when temp tables with the same name appear in nested modules. For this reason avoid short generic names like #t, #temp, etc. Or in this case #ins and #del. Call them #ins_employees (if this is the Employees table).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @KaiYang-6525,

Welcome to Microsoft Q&A!

It is recommended to provide the CREATE TABLE statements for your tables together with INSERT statements with sample data, the complete statement of your trigger.
We also need to see the expected result of the sample.

In addition, you could refer below trigger as a simple example and check whether it is a little helpful to you.

 CREATE TRIGGER mytrigger
 ON mytable
 FOR insert, update,delete AS
 declare @UserName  varchar(20),@empcode varchar(10);
    
 If exists (Select * from inserted) and not exists(Select * from deleted)
 begin
    select @UserName = EMP_ModifiedBy, @empcode = emp_cd from inserted
    select @UserName
    select @empcode
 end
    
 If exists(select * from deleted) and not exists(Select * from inserted)
 begin 
     select @UserName = EMP_ModifiedBy, @empcode = emp_cd from deleted
     select @UserName
     select @empcode
 end

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KaiYang-6525 avatar image
0 Votes"
KaiYang-6525 answered ErlandSommarskog commented

Hi Melissa,
Here is my entire code with create table statement.

first, I create a table to hold the information:
CREATE TABLE [dbo].[NewAuditLog](
[Type] [char](6) NULL,
[TableName] [varchar](128) NULL,
[PK] [varchar](1000) NULL,
[FieldName] [varchar](128) NULL,
[OldValue] [varchar](1000) NULL,
[NewValue] [varchar](1000) NULL,
[UpdateDate] [datetime] NULL,
[UserName] [varchar](128) NULL,
[N_count] [int]
) ON [PRIMARY]


Second, I use the trigger below to tracking all of DML actions :
CREATE TRIGGER [dbo].[TR_Employee_AUDIT] ON [dbo].[Employee_mstr] FOR UPDATE
AS

DECLARE @bit INT ,
@field INT ,
@maxfield INT ,
@char INT ,
@fieldname VARCHAR(128) ,
@TableName VARCHAR(128) ,
@PKCols VARCHAR(1000) ,
@sql VARCHAR(2000),
@UpdateDate VARCHAR(21) ,
@UserName VARCHAR(128) ,
@Type CHAR(1) ,
@PKSelect VARCHAR(1000),
@empcode VARCHAR(20)


SELECT @TableName = 'Employee_Mstr'

  • date and user
    SELECT @UserName = SYSTEM_USER ,
    @UpdateDate = CONVERT (NVARCHAR(30),GETDATE(),126)

  • Action
    IF EXISTS (SELECT FROM inserted)
    IF EXISTS (SELECT
    FROM deleted)
    SELECT @Type = 'Update'
    ELSE
    SELECT @Type = 'Insert'
    ELSE
    SELECT @Type = 'Delete'

  • get list of columns
    SELECT INTO #ins FROM inserted
    SELECT
    INTO #del FROM deleted

select @UserName = EMP_ModifiedBy, @empcode = emp_cd from #ins
if isnull(@UserName,'') = ''
select @UserName = EMP_ModifiedBy, @empcode = emp_cd from #del

  • Get primary key columns for full outer join
    SELECT @PKCols = COALESCE(@PKCols + ' and', ' on')
    + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    WHERE pk.TABLE_NAME = @TableName
    AND CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND c.TABLE_NAME = pk.TABLE_NAME
    AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

  • Get primary key select for insert
    SELECT @PKSelect = COALESCE(@PKSelect+'+','')
    + '''<' + COLUMN_NAME
    + '=''+convert(varchar(100),
    coalesce(i.' + COLUMN_NAME +',d.' + COLUMN_NAME + '))+''>'''
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    WHERE pk.TABLE_NAME = @TableName
    AND CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND c.TABLE_NAME = pk.TABLE_NAME
    AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

IF @PKCols IS NULL
BEGIN
RAISERROR('no PK on table %s', 16, -1, @TableName)
RETURN
END


SELECT @field = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
AND COLUMN_NAME NOT IN ('EMP_ModifiedOn','EMP_ModifiedBy')
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION > @field
AND COLUMN_NAME NOT IN ('EMP_ModifiedOn','EMP_ModifiedBy')
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = POWER(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) & @bit > 0
OR @Type IN ('I','D')
BEGIN
SELECT @fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field
AND COLUMN_NAME NOT IN ('EMP_ModifiedOn','EMP_ModifiedBy')
SELECT @sql = '
insert NewAuditLog ( Type,
TableName,
PK,
FieldName,
OldValue,
NewValue,
UpdateDate,
UserName)
select ''' + @Type + ''','''
+ @TableName + ''',''' + @empcode + ''',''' + @fieldname + ''''
+ ',convert(varchar(1000),d.' + @fieldname + ')'
+ ',convert(varchar(1000),i.' + @fieldname + ')'
+ ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''''
+ ' from #ins i full outer join #del d'
+ @PKCols
+ ' where i.' + @fieldname + ' <> d.' + @fieldname
+ ' or (i.' + @fieldname + ' is null and d.'
+ @fieldname
+ ' is not null)'
+ ' or (i.' + @fieldname + ' is not null and d.'
+ @fieldname
+ ' is null)'
EXEC (@sql)
END
END


I'm not sure where causes the problem

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This is now a triplicate post. See https://docs.microsoft.com/answers/questions/498851/my-dml-trigger-doesn39t-save-data-into-target-tabl.html where I have already posted an answer.

It's not a good use of people's time to post the same question multiple times.

(Also, when inserting code, use the button with ones and zeroes, to avoid that part of the code is taken to be markup.)

0 Votes 0 ·