March 21, 2006 at 10:41 am
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-2 INTEGER
DECLARE @SQLSTR AS VARCHAR(8000)
DECLARE @CURCOL SYSNAME
DECLARE @COLTYPE AS VARCHAR(10)
-- SETUP VARIABLES
SET @SQLSTR = ''
SET @CUR=1
SELECT @max-2 = MAX(ID) FROM @MEMTABLE
-- LOOP EVEY FIELD
WHILE @CUR <= @max-2
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-2 - 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.
edit: i solved this problem, added another column to audit_inventory
March 24, 2006 at 8:00 am
This was removed by the editor as SPAM
March 27, 2006 at 8:10 am
Go back to your previous post here
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=267157#bm267755
Sounds like you got htis to work maybe once then changed the table definition.
March 28, 2006 at 9:27 am
"changed the table definition"
thats exactly what i did.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply