trigger error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

trigger error

Author

Topic

mfdoom
Starting Member

10 Posts

Posted - 2006-03-21 : 12:36:39

all, i was using the following audit trigger on my inventory table:

DECLARE @ACT CHAR(6)
DECLARE @DEL BIT
DECLARE @INS BIT
DECLARE @SQLSTRING VARCHAR(2000)

SET @DEL = 0
SET @INS = 0

IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @DEL=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @INS = 1

IF @INS = 1 AND @DEL = 1 SET @ACT = 'UPDATE'
IF @INS = 1 AND @DEL = 0 SET @ACT = 'INSERT'
IF @DEL = 1 AND @INS = 0 SET @ACT = 'DELETE'

IF @INS = 0 AND @DEL = 0 RETURN

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[AUDIT_inventory]')
AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
BEGIN
-- CREATE A MEMORY TABLE CONTAINING THE FIELDS AND TYPES OF THE TABLE
DECLARE @MEMTABLE TABLE
(
ID INT IDENTITY
,COLUMNAME SYSNAME
,TYPENAME VARCHAR(20)
)
-- INSERT THE COLUMNAMES AND THE DATATYPES
INSERT @MEMTABLE
(COLUMNAME,TYPENAME)
SELECT NAME,TYPE_NAME(XTYPE)
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('[DBO].[inventory]')
ORDER BY COLID

DECLARE @CUR INTEGER
DECLARE @MAX INTEGER
DECLARE @SQLSTR AS VARCHAR(8000)
DECLARE @CURCOL SYSNAME
DECLARE @COLTYPE AS VARCHAR(10)

-- SETUP VARIABLES
SET @SQLSTR = ''
SET @CUR=1
SELECT @MAX = MAX(ID) FROM @MEMTABLE

-- LOOP EVEY FIELD
WHILE @CUR <= @MAX
BEGIN

-- GET VALUES FROM THE MEMTABLE
SELECT @CURCOL = COLUMNAME,@COLTYPE = TYPENAME FROM @MEMTABLE WHERE ID = @CUR
IF @COLTYPE = 'INT' OR @COLTYPE = 'BIGINT' OR @COLTYPE='UNIQUEIDENTIFIER'

-- WE DO WANT TO COPY INT/BIGINT/UNIQUEIDENTIFIER FIELDS BUT IF THEY ARE AN
-- IDENTITY OR A ROWGUIDCOLUMN WE DO NOT WANT TO COPY THOSE ATTRIBUTES

SET @SQLSTR = @SQLSTR + ' CAST(' + @CURCOL + ' AS ' + @COLTYPE + ')
AS ['+ @CURCOL + '] '
ELSE

-- ANOTHER FIELD DO NOTHING JUST COPY IT AS IT IS
SET @SQLSTR = @SQLSTR + ' ' + @CURCOL + @CURCOL + ' AS ['+ @CURCOL +'] '
IF @CUR <= @MAX - 1 SET @SQLSTR=@SQLSTR + @SQLSTR + ','
SET @CUR = @CUR + 1
END

-- ADD THE AUDIT FIELDS
SET @SQLSTR = @SQLSTR +',CAST('' '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS
TRG_DATE,system_user as TRG_USER'

-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
SET @SQLSTR = 'SELECT TOP 0' + @SQLSTR + 'INTO [DBO].[AUDIT_inventory] FROM [DBO].[inventory]'
EXEC(@SQLSTR)
END

IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_inventory] SELECT *,'INSERT', getdate() , system_user FROM INSERTED
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_inventory] SELECT *,'DELETE', getdate() , system_user FROM DELETED
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_inventory] SELECT *,'UPDATE', getdate() , system_user FROM INSERTED

and i get the following error:
Msg 213, Level 16, State 1, Procedure TRG_inventory, Line 88
Insert Error: Column name or number of supplied values does not match table definition.

i have no idea, any help would be appreciated.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-03-21 : 12:53:38

very simple.

1. What is line 88

2. What the error message basically means is that the values you are trying to insert are either different from what you have in your table or more/less than what in your table

I would advice you search out line 88 and post your table DDL structure here

Afrika

mfdoom
Starting Member

10 Posts

Posted - 2006-03-21 : 13:04:09

no problem:

-- ADD THE AUDIT FIELDS
SET @SQLSTR = @SQLSTR +',CAST('' '' AS CHAR(6)) AS TRG_ACTION,CAST(GETDATE() AS DATETIME) AS
TRG_DATE,system_user as TRG_USER'

-- SET UP THE SELECT FOR CREATING THE AUDIT TABLE
SET @SQLSTR = 'SELECT TOP 0' + @SQLSTR + 'INTO [DBO].[AUDIT_inventory] FROM [DBO].[inventory]'
EXEC(@SQLSTR) <- line 88
END

IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_inventory] SELECT *,'INSERT', getdate() , system_user FROM INSERTED
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_inventory] SELECT *,'DELETE', getdate() , system_user FROM DELETED
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_inventory] SELECT *,'UPDATE', getdate() , system_user FROM INSERTED

DDL structure for audit_inventory table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AUDIT_inventory](
[ID] [int] NULL,
[name] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[type] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TRG_ACTION] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TRG_USER] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO

mfdoom
Starting Member

10 Posts

Posted - 2006-03-21 : 13:08:26

yeah, i just answered my own question

modified the audit_inventory table to include trg_date

thanks for pointing me in the right direction though

mfdoom
Starting Member

10 Posts

Posted - 2006-03-21 : 13:11:43

scratch my previous post, i just broke it more, any info would be appreciated.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-03-21 : 13:14:04

were is line 88 ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-21 : 13:25:22

This is obviously code for a trigger since you refer to inserted and deleted tables yet you didn't not post the entire trigger code (there is no create trigger statement or GO statment).

I don't understand why you are using information_schema views to assemble a dynamic sql statement. This code is obviously custom to the inventory table so why not just code non-dynamic sql to perform the insert to the audit table? Also, it seems like you are creating the audit table from within the trigger code??? How does that work (more than once)?

EDIT:
Ok, I see now that most of this code is to create your audit table if it doesn't exist. But I still think even the check for its existance is additional overhead you don't need considering it's checking on every insert, update, and delete.

Be One with the Optimizer
TG

mfdoom
Starting Member

10 Posts

Posted - 2006-03-21 : 13:28:47

actually afrika, you helped me out alot, i modified the audit_inventory table include another column for the timestamp so its working now.

thanks so much for your help!

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-03-21 : 16:11:37

Glad to help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources