October 17, 2005 at 8:06 am
Hello,
i hope you can help me further... my problem is i have only ther column id for a select. but how can i execute the sql? dynamic sql is bad because i have no access to the inserted table when i use "exec" how can i get this sql working?
SELECT TOP 1 COL_NAME(object_id(@tblname), @intCountColumn) AS TEST FROM INSERTED
regards
andreas
October 17, 2005 at 8:18 am
What are you trying to do exactly??
October 17, 2005 at 8:21 am
Hello,
i want to use it in a trigger...
----------ALTER Trigger tr_admin
-- list all columns that were changed
On ArtikelT
For Update
As
SET NOCOUNT On
declare @intCountColumn int, @n int, @tblname sysname
DECLARE @AlterWert nvarchar(100)
DECLARE @NeuerWert nvarchar(100)
select @tblname = object_name(parent_obj) from sysobjects where name = object_name(@@procid)
-- count columns in the table
SELECT @intCountColumn = Count(Ordinal_position)
FROM INFORMATION_SCHEMA.COLUMNS a
WHERE TABLE_NAME = @tblname
-- list column names
while @intCountColumn > 0
begin
set @NeuerWert = (SELECT TOP 1 COL_NAME(object_id(@tblname), @intCountColumn) AS TEST FROM INSERTED)
set @AlterWert = (SELECT TOP 1 COL_NAME(object_id(@tblname), @intCountColumn) AS TEST FROM DELETED)
set @n = ceiling(@intCountColumn / 8.0)
if substring(COLUMNS_UPDATED(), @n, 1) & power(2, (@intCountColumn-8*(@n-1)-1)) > 0
INSERT INTO auditt (tabelle, Attribut, rowid, Operation, feldalterwert, FeldNeuerWert)
VALUES (@tblname, COL_NAME(object_id(@tblname), @intCountColumn), @intCountColumn, 'U', @AlterWert, @NeuerWert)
Set @intCountColumn = @intCountColumn - 1
End
-------------------
maybe wrong approach?
regards
andreas
October 17, 2005 at 8:29 am
Still have no clue of what you really need to do. What do you need to audit, how do you have to audit it?
October 17, 2005 at 8:38 am
i want to audit important tables... this trigger should audit the old and new value of each column that is changed...
regards
andreas
October 17, 2005 at 8:47 am
It can be done in a set based approach.
This is an exemple of a way to do it in Yukon... I'll try to see if I can do it in sql server 2K..
http://www.sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk
October 17, 2005 at 11:44 am
To get access with dynamic sql you need to copy the data into a temp table.
This is how you do it in SS 2k:
http://qa.sqlservercentral.com/columnists/lPeysakhovich/anaudittrailgenerator.asp
Now, I have never agreed with the idea of dynamic sql in a trigger code for multilple reasons (if you need them let me know ) but I have no clue about your environment. For high performance environment is simply a no-no.
Cheers,
* Noel
October 17, 2005 at 11:52 am
What if a trigger code generator was built so that the trigger would use static sql?
October 17, 2005 at 12:07 pm
That is the Right approach!!
Many people try to justify dynamic sql with the Idea that the columns and tables are very volatile in their environment. That is plain wrong. If you add or remove columns you can definitely at THAT time invoke your generator and change the trigger with it !!!
Then no need to have Dynamic SQL, No Need to copy inserted and deleted entirely on tempdb, no need to compile the code every time the trigger is fired, therefore: less overhead an good efficiency
Cheers,
* Noel
October 17, 2005 at 12:10 pm
I'm gonna go on a limb here and say that it shouldn't be too hard to write such a generator (especially since I ain't gonna try to write it ATM ). All the info is already in SysColumns, so it's just concatenation work from there.
October 17, 2005 at 12:18 pm
Right again!
Not hard to write, Very simple to maintain and your final product should look neat with static fields only
* Noel
October 20, 2005 at 6:43 am
hi,
but how can i do this when i only have to audit the edited columns... only at runtime i know them...
regards
andreas
October 20, 2005 at 7:22 am
or do you think it is better to make for each table in the app an audit table... there are about 200-250 in right now...
regards
andreas
October 20, 2005 at 7:26 am
One audit table per table... Easier to keep the structure in sync and the audit script is a breeze to write.
October 20, 2005 at 7:36 am
hm yes... it would be more easy... all columns... no worry about ntext fields...
you have good expieriences with this approach...
my idea was to keep the db small... but its more diff... i think i change to audit tables for each normal...
thak you for the messages
andreas
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply