SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
12.3.427: Help with code snippet / macro

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
12.3.427: Help with code snippet / macro
Author Message
Mindflux



Joined: 25 May 2013
Posts: 805
Country: United States

Post 12.3.427: Help with code snippet / macro Reply with quote
In relation to the trigger post I made a minute ago:

Code:
DISABLE TRIGGER $OBJECT(trigger, ins_object)$ ON $OBJECT(table, ins_object)$;


outputs something like

Code:
DISABLE TRIGGER TABLA_UTrig ON TABLA_UTrig;



Is there a way to make the object/table part of the macro seek out the owner of the trigger?

Since my naming conventions are solid enough this code does work:

Code:
DISABLE TRIGGER $OBJECT(trigger, ins_object)$ ON $$ SELECT LEFT('$OBJECT(trigger, ins_object)$',CHARINDEX('_','$OBJECT(trigger, ins_object)$')-1) $$


which then outputs


Code:
DISABLE TRIGGER TABLEA_UTRIG ON TABLEA;


Right now since my naming conventions are fine.. though I may change that to use sys.tables joined to sys.triggers instead.

BUT really, the question is: is there a way to make $OBJECT(table,ins_object)$ smarter regarding this? Am I missing some simple way of doing this without the custom macro?
Fri May 05, 2023 12:17 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 805
Country: United States

Post Reply with quote
Code:
DISABLE TRIGGER $OBJECT(trigger, ins_object)$ ON $$ SELECT t.NAME FROM sys.tables AS t INNER JOIN sys.triggers AS t2 ON t2.parent_id = t.object_id WHERE t2.name = '$OBJECT(trigger, ins_object)$' $$
|
ENABLE TRIGGER  $OBJECT(trigger, ins_object)$ ON $$ SELECT t.NAME FROM sys.tables AS t INNER JOIN sys.triggers AS t2 ON t2.parent_id = t.object_id WHERE t2.name = '$OBJECT(trigger, ins_object)$' $$


This works good and will not break if for some reason my naming convention changes...
Fri May 05, 2023 12:21 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
After reading your initial post I was about to suggest a similar thing, use $$..$$ to create the final output.
Fri May 05, 2023 2:32 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 805
Country: United States

Post Reply with quote
SysOp wrote:
After reading your initial post I was about to suggest a similar thing, use $$..$$ to create the final output.


So at this time $OBJECT(...)$ cannot resolve dependencies/parents/children in any meaningful way?
Fri May 05, 2023 2:33 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Quote:
So at this time $OBJECT(...)$ cannot resolve dependencies/parents/children in any meaningful way?


Not that as far as triggers and tables go. If there are several macros bound to a single table select, or column selection for table, it can handle that. But parents and children it cannot tie together.
Fri May 05, 2023 5:56 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.