SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


variable columns in select statements?


variable columns in select statements?

Author
Message
cew3
cew3
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 23
Hi there,

Scenario:

3 tables:
- config (defines e.g. which columns in which table should be written to timestamp table)
- mydata
- timestamp

I have to create an insert trigger for table mydata which does the following:

fetch the values from last insert into mydata and write some mydata columns, which are defined in the config table, into table timestamp with the following structure:
timestamp.tablename = 'mydata'
timestamp.fieldname = columnname
timestamp.date = actual date
timestamp.value = value of column

In the final version this should be done by a stored procedure called with tablename as parameter.

The majo problem now is to fetch the desired columns, which I get out of the config table, from the inserted row.
In the first attempt I used a loop over the selected fieldnames and tried to use @fieldname in "SELECT @fieldname FROM inserted".

But this doesn't work because variable column names don't seem to be allowed. Crying

Another point is, that the columns to written into the timestamp table have different data type (integer, string, date, binary, text, ...)
Can I use text for the timestamp.value column and convert the data before inserting?


Any suggestions how to solve this problem?


Best regards,
cew3
qtsohg
qtsohg
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 29
Am I right in understanding that the Trigger that gets called will call your stored procedure?

If this is the case, all you need to do is get the identity (Good practise is to always have a Identity Column anyway) that was insterted into the mydata table and pass it to the Stored Procedure. Then you can build a SQL String that you can modify with Variables.


Regarding the the Type in the timestamp column, you can use varchar or nvarchar and cast your value to a string?

Or if you want to, you could use sql_variant.

The choice is up to you.

Let me know if this was helpful!

Pierre
cew3
cew3
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 23
Hi Pierre,

at the moment I'm tesing the complete functionality just in the trigger.
But this is not the main problem.
I fetched the identity from 'inserted' and could fetch now the complete row from mydata instead of inserted.

The major problem is that I can't build a SELECT with variable column name. Using @fieldname in the where clause is possible.

I tried already to build a complete query string and execuse with EXECUTE. Won't work....

In Sybase Advantage Database Server the following works fine:

@tbname = 'mydata';

DECLARE fieldlist CURSOR AS SELECT fieldname FROM config WHERE tablename = @tbname AND triggered = True;

OPEN fieldlist;

WHILE FETCH fieldlist DO
@fieldname = fieldlist.[fieldname];
@s = 'INSERT INTO timestmp(tablename,fieldname,recordid,timestamp,oldvalue)
SELECT
'''+@tbname+''',
'''+@fieldname+''',
emrn_id,
now(),
'+@fieldname+'
FROM __new';
EXECUTE IMMEDIATE @s;
END WHILE;

// __new is equivalent to mssql's inserted


Any ideas?

cew
qtsohg
qtsohg
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 29
Can you post the error that you are getting? Also the code in the Trigger so I can have a look at it?

In theory you can write something like this:
DECLARE @s varchar(2000);

SET @s = 'INSERT INTO timestmp(tablename,fieldname,recordid,timestamp,oldvalue)'
SET @s = @s + 'SELECT '''+ @tbname + ''','''+@fieldname+''', , now(), ' + @fieldname + ' FROM ' + @tbname
SET @s = @s + 'WHERE = ' + @Identity

EXECUTE @s;



Ok, i have excluded the cursor too loop through each field etc. If you wish to get the old value just make sure you sue the BEFORE instead of AFTER statement.

If you are still having issues, please post code and error for me Smile
cew3
cew3
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 23
Hi,

i tried the folloewing:



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





ALTER TRIGGER [InsTrigger] ON [dbo].[emrn]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON

DECLARE @tbname varchar(100)
DECLARE @fieldname varchar(50)
DECLARE @id varchar(100)
DECLARE @s varchar(2000)

select @tbname = 'emrn'

DECLARE triggeredfields CURSOR
FOR
SELECT fieldname
FROM dbconfig
WHERE tablename =@tbname AND triggered = 1

OPEN triggeredfields

FETCH NEXT FROM triggeredfields INTO @fieldname



WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @id = (SELECT emrn_id FROM inserted)

SET @s = 'INSERT INTO timestmp(tablename,fieldname,recordid,timestamp,oldvalue) '
SET @s = @s + 'SELECT '''+ @tbname + ''','''+@fieldname+''','+@id+', now(), ' + @fieldname + ' FROM ' + @tbname + ' '
SET @s = @s + 'WHERE emrn_id = ' + @id

EXECUTE @s;

FETCH NEXT FROM triggeredfields INTO @fieldname
END

CLOSE triggeredfields
DEALLOCATE triggeredfields

END




Inserting a row into emrn will still produce the (german) error message:


Der Name 'INSERT INTO timestmp(tablename,fieldname,recordid, [timestamp],oldvalue) SELECT 'emrn','emrn_name',3,now(), emr_name FROM emrn WHERE emrn_id=3' ist keine gültiger Bezeichner.



if I run this query on console it works...


Regards
cew
qtsohg
qtsohg
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 29
Lucky you that I am fluent in German Wink

Try using sp_executesql instead of Execute. Otherwise I can only recommend to move the bulk of the functionality into a Stored Procedure and calling that from the Trigger.

Let me know if the sp_executesql worked and if the Error message Changed.
sgmunson
sgmunson
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21562 Visits: 4664
If I recall correctly, the EXECUTE statement needs the @s to appear in parentheses, as follows:


EXECUTE (@s);



Steve
(aka smunson)
SmileSmileSmile

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search