SELECT Columnname - how do get the result with the columnid

Question

Post reply

SELECT Columnname - how do get the result with the columnid

SkAudi

SSC-Addicted

Points: 400
More actions
October 17, 2005 at 8:06 am

#91774

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

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

October 17, 2005 at 8:18 am

#597629

What are you trying to do exactly??

SkAudi SSC-Addicted Points: 400 More actions · Answer 2

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Ninja's_RGR'us

SSC Guru

Points: 294069

October 17, 2005 at 8:29 am

#597637

Still have no clue of what you really need to do. What do you need to audit, how do you have to audit it?

SkAudi SSC-Addicted Points: 400 More actions · Answer 4

SkAudi

SSC-Addicted

Points: 400

October 17, 2005 at 8:38 am

#597641

i want to audit important tables... this trigger should audit the old and new value of each column that is changed...

regards

andreas

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

October 17, 2005 at 8:47 am

#597647

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

noeld SSC Guru Points: 96590 More actions · Answer 6

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us

SSC Guru

Points: 294069

October 17, 2005 at 11:52 am

#597710

What if a trigger code generator was built so that the trigger would use static sql?

noeld SSC Guru Points: 96590 More actions · Answer 8

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Ninja's_RGR'us

SSC Guru

Points: 294069

October 17, 2005 at 12:10 pm

#597716

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.