April 15, 2008 at 4:56 pm
Fine, turn the trigger back on, then PRINT the query instead of executing it.
Analyse that query and you'll find your problem there.
Also you are asking for troubles by auditing this way. It's been proven to be more taxing on the server that way.
April 15, 2008 at 6:04 pm
Msg 8152, Level 16, State 14, Procedure p_trigger, Line 61
String or binary data would be truncated.
The statement has been terminated
This message means that the string data you trying store in a field is longer than the field length. You need define which field length is bigger.
April 15, 2008 at 6:07 pm
Check field tblnm varchar(50) and variable @tblname VARCHAR(128)
April 15, 2008 at 6:11 pm
Ninja's_RGR'us (4/15/2008)
Fine, turn the trigger back on, then PRINT the query instead of executing it.Analyse that query and you'll find your problem there.
Also you are asking for troubles by auditing this way. It's been proven to be more taxing on the server that way.
It depend from the database and data size. For small databases, for example medical trials, when you have 200 databases per year but each one 500M it is working very well.
April 15, 2008 at 11:55 pm
My client has a small in-house system with only 8 users on it, no website. The difference between both trigger system's performance is enough to keep him from buying another 20K server.
Ok, the server is already pretty loaded with all it has to do besides SQL, but every little thing count. Since I had to chose a trigger code generator, I chose one that did the job with better performance. Same time to implement, save money on hardware down the line (not always, but it will happen).
April 16, 2008 at 10:22 am
ok, without having to completely scrap my current setup (just reworking my triggers), how can I modify my solution for best performance ?
April 16, 2008 at 10:36 am
You can find a trigger generator that will make all the code necessary for the triggers.
Or you can build one. It may take a few hours of work, but rebuilding the full setup is usually not that hard when you keep it short and simple.
Sorry but I don't have my code with me so I can't share.
April 16, 2008 at 1:38 pm
Is there a site or article that will lead me in the right direction?
April 16, 2008 at 4:36 pm
I need some one to walk me through this, step by step - Ill show you what I have, you tell me what to do, etc. Apparently I don't know what I am doing wrong. I have tried all of the previously mentioned troubleshooting, and I still come up with nothing. Does anyone know where I can find a step by step guide to programming an easy, effective and fast Auditing solution with SQL Server 2005? My life depends on this.....
April 16, 2008 at 7:05 pm
You life depends on this...
You need to answer a couple of questions...
1. Would you be happy with a non-dynamic SQL solution?
2. Are any TEXT, NTEXT, or IMAGE columns involved? If so, what should be done with them?
3. Do you have a structure for the AuditTable in mind? If so, please post the CREATE TABLE script for the AuditTable. If not, do you have any requirements you must meet?
4. Speaking of requirements, if you life depends on this, has anyone given you any requirements? If so, please attach them as a file or, if short enough, post those requirements.
--Jeff Moden
April 16, 2008 at 7:29 pm
At a minimum you need to 1) script out the a_District table and 2) do as Ninja asked and add a "Print @cmd" to your p_trigger procedure right before the "Exec (@cmd)". Then post both of these things here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 17, 2008 at 9:19 am
My audit table (for history of District)
USE [JuryDB]
GO
/****** Object: Table [dbo].[a_District] Script Date: 04/17/2008 09:14:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[a_District](
[DistrictNum] [int] NOT NULL,
[Region] [varchar](50) NOT NULL,
[D_Addr1] [varchar](50) NOT NULL,
[D_Addr2] [varchar](50) NULL,
[D_City] [varchar](50) NOT NULL,
[D_Phn] [varchar](15) NULL,
[D_Tollfree] [varchar](15) NULL,
[D_Zip] [varchar](10) NOT NULL,
[D_JurPhn] [varchar](15) NOT NULL,
[D_State] [char](2) NOT NULL,
[Budget_Org] [varchar](50) NULL,
[Cost_Org] [varchar](50) NULL,
[Cost_Mile] [varchar](50) NULL,
[Attend_Fee] [varchar](50) NULL,
[row_id] [int] NULL,
[modid] [int] NOT NULL,
[audit_id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK__a_District__28B808A7] PRIMARY KEY CLUSTERED
(
[audit_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-----------------------------------------------------------------------------
AGAIN THE P_TRIGGER - THIS TIME I HAVE MARKED THE LINE IN WHICH THE TRANSACTION HALTS ON:
/***********************P_TRIGGER*************************************/
USE [JuryDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_trigger]
@tblname VARCHAR(128)
as
BEGIN
declare @cnti int, @cntd int, @maxid int, @minid int, @cmd varchar(4000), @audittblname varchar(128),
@cmdINSERT varchar(1000), @cmdSELECT varchar(1000), @modif_id bigint,
@cmdFROM varchar(255), @AUDITINSERT varchar(255), @DUI varchar(10)
set @audittblname = 'a_' + substring(@tblname,1,128)
declare @Tmp table (cname varchar(128), cid int identity(1,1) )
select @cnti = count(*) from #ti
select @cntd = count(*) from #td
-- check how many rows changed. If 0 then do nothing
IF (@cnti = 0 and @cntd = 0)
return 0
-- get all table columns
insert into @Tmp(cname)
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tblname
select @maxid = max(cid), @minid = min(cid) from @Tmp
set @cmdSELECT = ' SELECT '
set @cmdINSERT = ' INSERT INTO ' + @audittblname + ' ( '
while (@minid <= @maxid)
begin
-- begin while
select @cmdINSERT = @cmdINSERT + cname + ',' , @cmdSELECT = @cmdSELECT + 'd.' + cname + ','
FROM @Tmp
where cid = @minid
set @minid = @minid + 1
end
-- end while
-- always set new rows for the AUDIT
IF (@cnti = @cntd )
begin
set @DUI = ' ''U'' '
SET @cmdFROM = ' FROM #ti d '
end
IF (@cnti < @cntd)
begin
set @DUI = ' ''D'' '
SET @cmdFROM = ' FROM #td d '
end
------------------------ERRORS DISPLAY ON THIS LINE, OF COURSE THERE IS SOMETHING-------
------------------------FISHY HAPPENING BEFORE IT HITS THIS POINT-----------
------------------------DOES NOT GET PAST THIS POINT----------------
IF (@cnti > @cntd) PRINT 'HERE IS WHERE MY APPLICATION STOPS'
begin
set @DUI = ' ''I'' '
PRINT @DUI
SET @cmdFROM = ' FROM #ti d '
end
-- insert record into table modif
insert into modif(tablenm, dml_type)
select @tblname, @dui
PRINT @tblname
PRINT @dui
-- get identity
select @modif_id = SCOPE_IDENTITY( )
-- add modification column value
set @AUDITINSERT = ' modid )'
set @cmd = @cmdINSERT + @AUDITINSERT + @cmdSELECT + cast(@modif_id as varchar) + @cmdFROM
PRINT N'CMD: '+ @cmd+N'';
exec (@cmd)
SET NOCOUNT OFF
END
PRINT N'CMD2: '+ @cmd+N'';
GO
April 17, 2008 at 9:22 am
ALSO - YES, I WOULD BE HAPPY WITH A NON - DYNAMIC SQL SOLUTION
REQUIREMENTS
AFTER EACH UPDATE, INSERTION, OR DELETE
I NEED TO CAPTURE ALL INFO IN RECORD BEFORE CHANGE, ALL INFO AFTER CHANGE, THE TYPE OF CHANGE THAT OCCURRED, WHICH USER MADE THE CHANGE, THE TIME AND DATE OF THE CHANGE
June 8, 2008 at 4:10 pm
The problem with the sp is that the value 'I' is being inserted into a field defined as char(1), so this sp would never work.
Change 'I' to ''I'' and it should work. This same change should be made for the other transaction types also.
IF (@cnti > @cntd)
BEGIN
set @DUI = ' ''I'' ' --should be set @DUI = 'I'
SET @cmdFROM = ' FROM #ti d '
END
-- insert record into table modif
insert into modif(tablenm, dml_type)
select @tblname, @dui
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply