p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

sql_language thread: Trigger Syntax Prob


Message #1 by "Ed Atwell" <shogun@b...> on Fri, 1 Dec 2000 20:02:34 -0500
Exec() was the key, thank you all for the help, much appreciated.
Ed

-----Original Message-----
From: Harsh Nandu [mailto:harsh_stgil@h...]
Sent: Monday, December 04, 2000 12:37 AM
To: sql language
Subject: [sql_language] RE: Trigger Syntax Prob


Hi,

Your trigger has to :
- Create the update statement as a string.
- Then use the exec/execute keyword with the string for actual updates.

eg. EXEC ('update ' + @tablename + set .....)

Regds,
Harsh


>From: José Ernesto Echeverría <ernestoe@s...>
>Reply-To: "sql language" <sql_language@p...>
>To: "sql language" <sql_language@p...>
>Subject: [sql_language] RE: Trigger Syntax Prob
>Date: Sat, 2 Dec 2000 19:35:28 -0600
>
>If that is your case, you might want to try executing dynamic SQL
>statements, with commands such as 'EXECUTE' or 'EXECUTE IMMEDIATE' (check
>SQL Books On Line). I haven't tried these myself, but it works similarly in
>Oracle.
>
>You usually prepare the SQL String to be executed, and then execute it
>either through these commands, or a similar stored procedure. If you are
>programming in a different language (i.e. Visual Basic), you can try to
>move
>this kind of logic to the application tier instead of the database tier.
>
>Regards.
>
>-----Original Message-----
>From: Ed Atwell [mailto:shogun@b...]
>Sent: Saturday, December 02, 2000 2:44 PM
>To: sql language
>Subject: [sql_language] RE: Trigger Syntax Prob
>
>
>Good Question,
>Thought of that one myself, trust me.
>Problem is this, every time a new department is added, new set of tables is
>added. Infinite number of departments can be added, and trust me, due to
>the
>revenue model associated with the project(or lack thereof...), a whole
>bunch
>better be. :)
>Thanks for considering my question though. Any help is appreciated. My
>T-SQL
>is a little weak.
>Cheers,
>Ed
>
>-----Original Message-----
>From: José Ernesto Echeverría [mailto:ernestoe@s...]
>Sent: Saturday, December 02, 2000 1:33 PM
>To: sql language
>Subject: [sql_language] RE: Trigger Syntax Prob
>
>
>Before suggesting, I'd like to know "how many" different tables are
>considered in this logic. Does the table name really need to be dynamic, or
>can it be "hardcoded" in the logic of the trigger?
>
>Regards.
>
>-----Original Message-----
>From: Ed Atwell [mailto:shogun@b...]
>Sent: Friday, December 01, 2000 7:03 PM
>To: sql language
>Subject: [sql_language] Trigger Syntax Prob
>
>
>Hi All,
>I have a 'master' table of product information for several departments.
>When
>this table gets updated I want to fire a trigger that updates another table
>based on department. Easy enough, I realize.
>Heres the problem, the other table that is to be updated is unknown until
>runtime. The determination is made by a department id being passed in as
>part of the updated data in the master table. I run a quick select from yet
>a third table to determine which table to update  based on the department
>id.
>Before Anyone decides to question the logic of doing this to start with, I
>do have a good reason and it is turning into a long story, but I digress...
>So, does anyone know if I can use a variable in my statement UPDATE
><variable_table> SET....
>and so on.
>I keep getting Syntax errors.
>Thanks,
>Ed
>
>___________________________________
>

  Return to Index