July 9, 2008 at 1:39 am
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
July 9, 2008 at 3:31 am
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
July 9, 2008 at 3:53 am
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-2 = 'INSERT INTO timestmp(tablename,fieldname,recordid,timestamp,oldvalue)
SELECT
'''+@tbname+''',
'''+@fieldname+''',
emrn_id,
now(),
'+@fieldname+'
FROM __new';
EXECUTE IMMEDIATE @s-2;
END WHILE;
// __new is equivalent to mssql's inserted
Any ideas?
cew
July 9, 2008 at 4:11 am
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-2 varchar(2000);
SET @s-2 = 'INSERT INTO timestmp(tablename,fieldname,recordid,timestamp,oldvalue)'
SET @s-2 = @s-2 + 'SELECT '''+ @tbname + ''','''+@fieldname+''', , now(), ' + @fieldname + ' FROM ' + @tbname
SET @s-2 = @s-2 + 'WHERE = ' + @Identity
EXECUTE @s-2;
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 🙂
July 9, 2008 at 6:03 am
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-2 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-2 = 'INSERT INTO timestmp(tablename,fieldname,recordid,timestamp,oldvalue) '
SET @s-2 = @s-2 + 'SELECT '''+ @tbname + ''','''+@fieldname+''','+@id+', now(), ' + @fieldname + ' FROM ' + @tbname + ' '
SET @s-2 = @s-2 + 'WHERE emrn_id = ' + @id
EXECUTE @s-2;
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
July 9, 2008 at 8:14 am
Lucky you that I am fluent in German 😉
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.
July 10, 2008 at 2:16 pm
If I recall correctly, the EXECUTE statement needs the @s-2 to appear in parentheses, as follows:
EXECUTE (@s);
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply