January 29, 2009 at 7:33 am
Hi
Does anyone know of a way to use dynamic SQL inside a trigger, in conjunction with the inserted/deleted pseudo-tables of that trigger?
We have an issue where a table can have columns added dynamically by users, and a subset of the columns on that table can be defined as searchable by our application, again dynamically.
We need to copy the searchable field values from this table to a separate table for use in searching. BUT, since the fields/columns we have to handle are going to be dynamically defined, we need a way to pick the values we need out of the inserted table dynamically - the trigger can't be static code.
If anyone knows of a way to use the inserted table as an input to a dynamic SQL statement, I'd be MOST grateful to hear about it!
Thanx
David.
January 29, 2009 at 7:36 am
The data in the inserted table can be used quite easily for dynamic SQL, same as any other data. If what you want is the new columns, I think you'd need to use a DDL trigger to accomplish that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2009 at 7:51 am
GSquared
Problem is that we've got to use dynamic SQL first, to construct a SELECT statement that'll pull data from the inserted table, as opposed to using the data from the inserted table to build a dynamic SQL statement.
The way the application is constructed, each searchable field in the source table becomes a row in the search table. The search table just consists of work ID, column name & value.
Any ideas?
David.
January 29, 2009 at 8:14 am
I'm not going to attack the design too much, but it sounds like you are on quite the slippery slope. If this is a new design, you may want to re-think allowing dynamic schema changes. There are better ways to deal with User-Defined fields that do not involve all of this complication.
Now, as far as solving your immediate issue. I think your trigger only needs to change dynamically when a new column is actually added. So, why not use a DDL trigger that creates the static trigger code whenever a new column is added? In your DDL trigger, you could use service broker - add a row to your broker queue and use the broker processing procedure to dynamically alter your trigger.
January 29, 2009 at 8:26 am
Yes, process follows something like:
1. determine columns being added
2. drop trigger
3. create trigger
4. insert records
Job done, if you're worried about the logistics of it all, insert the new column list into a temp table then compile the new trigger and then insert the records.
Max
January 29, 2009 at 8:52 am
I think I understand what you're trying to do.
A user adds a column to a table, and then adds values to that column. You want to take the values from that column and add them to a name-value table as they are inserted into the parent table.
While I'm not sure that's the way I'd solve the business need, it sounds like you should be able to solve that by storing meta data about what columns in the table are NOT user-defined, and then using sys.columns to build a list of the columns that ARE user-defined by comparing it to the list of ones that are not.
Something like:
select name
from sys.columns
where object_id = object_id(N'dbo.MyTableName')
and name not in
(select ColumnName
from dbo.NonUserColumns)
From that select, you could build a dynamic statement that would insert those values into the name-value table.
Code like that could become quite slow, and since triggers hold locks and hold up transactions while they execute, you might not want that in a trigger. If you have to have it in one, definitely test like crazy to make sure it doesn't create deadlocks when multiple users insert multiple columns each, or when multiple users try to update the same rows at the same time.
Personally, I'd avoid this solution. Either have a fixed number of columns in the table called things like "User01", "User02", or have an XML column called "UserData" and store the data there. The XML solution will work better if the users aren't limited to the number of custom columns they can create.
Either one of those violates normal form pretty heavily, but there's no way around that if you're allowing users to create their own columns (unless all your users are proficient DBAs, of course).
Either of those also has a solution for what to do if a certain user is no longer valid. What do you do with the custom columns a user added if that user is no longer accessing the database? In the way you seem to be heading, you either leave them in the database, messing up your schema, or you have to actually drop columns from the tables, and drop rows from the name-value store. In the "User1", "User2" or the XML solution, you don't have to do either thing if you don't want to, and you can delete their rows if you do want to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 31, 2009 at 5:16 pm
Why not use a ddl trigger to populate a table with relevant field names and use that table to build dynamic SQL in the trigger on the insert
January 31, 2009 at 10:53 pm
David F. Straker (1/29/2009)
Does anyone know of a way to use dynamic SQL inside a trigger, in conjunction with the inserted/deleted pseudo-tables of that trigger?
Yes. Use a temporary table:
create table fufu (A int, B Int);
go
Create trigger trgFuFu on fufu for Update, Insert As
Begin
Select * Into #inserted From inserted
Exec('Select * from #inserted')
End
go
Insert into fufu Select 1,2
go
drop table fufu
[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]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply