|
|
Posted by Erland Sommarskog on 05/13/07 17:59
Ots (otsmcgee@yahoo.com) writes:
> It would seem straightforward enough to build the trigger as a varchar
> string, looping through the table's column names and inserting the 15 or
> so lines that check for an insert, update, or delete for each.
> Unfortunately, I'm stuck w/ SQL 2000, and would quickly exceed the 8000
> character limit.
I've already said that T-SQL is a very poor tool for the task.
> I could use something like VB to create the text of the trigger, and
> then just cut and paste it into Query Analyzer, but my employer wants
> this done as a stored procedure (they want to limit the number of
> external applications that have to be maintained).
We don't want too many tools in this company! You must not use hammers!
We have very good screwdrivers! Use them to get the nails in place!
Doing this in a stored procedure in SQL 2000 is just sheer stupidity.
In SQL 2005, it's different, as you could put your VB code in a CLR
stored procedure. And if you still like to hurt yourself and do string
manipulation in T-SQL, you could use nvarchar(MAX).
I'll tell you what, if your employer is that boneheaded, tell him
that you really must use Screwdriver 2005 for this, but the Express version
is sufficient. That is, download and install an instance of SQL Express
on the same machine, and set up a linked server. Built the triggers on
SQL Express, and then use EXEC() AT to deploy them on SQL 2000.
Of course, you will still do the job faster with a VB or a Perl hammer,
assuming that you have experience of string manipulation in these
languages. Then again, since you are employed and not a consultant that
charge by the other, maybe your employer doesn't care.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|