CreateTriggerdelete

Toad Data Modeler
1

I'm using MSSQL 2005 and Toad Data Modeler 2.25.
I want to genereate cascade delete triggers, but the deafault behaviour is to create AFTER DELETE triggers which is not useful since foreign key constrains kicks in before this time.
I modified CreateTriggerDelete to create a INSTEAD OF DELETE trigger which works OK, but I need to delete from current table in addition to the child tables as well:

delete %tablename% from %tablename% t, deleted d
where t.key = d.key

How to I generate the key part? I tried using @ForRelPk() but it keeps crashing..

Thanks!

formatter_package.gif
formatter_package.gif685×548 22.9 KB

2

Hi,

Please write us how exactly you modified the code. Unfortunately, we’re not sure if we understand well. More information would help us. Thanks in advance.

=====
Note: In this community, only issues on TDM3 are disscussed. So, please post your other future questions on TDM2 to support@quest.com. Thanks!

Regards,

Vladka

3

The CreateTriggerDelete was modified using the Templates Editor.
The modified function can be viewed in the attached file in the first post (t.txt)
The problem is that I want to generate the where part of the delete statement
Ex.

delete myTable from myTable t, deleted d
where t.myKey = d.myKey

Regards
Inge

4

Hello Inge,

Sorry, we’ve overlooked your attachment. :wink:

See our reply:

@ForRelPk( tb , tb , “t.[%tablename%] = d.[%tablename%]” , " and %cr%", “”)

… this, what you commented in t.txt, is generated in template TriggerParentDelete that should be run on line:
@forchild("", “”, template(TriggerParentDelete), “”, “”)

If you like to generate it directly in the script, it should be enough if you write this:
@forchild("", “”, ForRelPk( tb , tb , “t.[%tablename%] = d.[%tablename%]” , " and %cr%", “”), “”, “”)

Please let us know if it helps. Thanks!

Regards,

Vladka + Mario

5

Yes, if I replace %tablename% with %PkChildName%, it works by magic :

@forchild("", “”, ForRelPk( tb , tb , “t.[%PkChildName%] = d.[%PkChildName%]” , " and %cr%", “”), “”, “”)

This generates “t. = d.” for current table.

Does it exist any documentation of these functions somewhere?

Thanks!
Inge.

6

Hello Inge,

Happy to hear that the information helped.

Unfortunately, there is not a complete documentation on this. Nevertheless, some basic information can be found in the IScript.pdf doc (please see the attachment).

Have a nice day.

Vladka

Note for other readers: This thread applies to TDM2!
tdm_iScript.pdf (215 KB)