Toad World® Forums

After Script


#1

Hello TDM Community,

a table has the ability to fire sql statements (for example INSERT DML statements) after the script of the table DDL statement. but this is not true for all DDL statements, cause the DML statements will only be behind the CREATE statement, but not behind other DDL statemenst like keys. Is that the way it is intended or should the “After Script” code behind ALL DDL statements ?

IKrischer


#2

Hi Ilja,
are you talking about some specific model or processing, importing, synchronizing or reverse engineering? Could you send a piece of code showing it?
Anyway, you can change order of generated objects- under menu Model select Order of Generated Objects. On bookmark General you can shift the options up and down in the order, on Extensions bookmark click on some value, press F2 and a roll-up menu with available options appear.

Regards,
Lukas


#3

hello Lukas,

I am not talking about reverse engineering, I am talking about a created table. When you open the object (table) you have can add SQL Code in the “AFTER Script” section. That SQL Code is only behind the create statement of the table, but not behind the keys, for example a unique key. To change the Order of Genrereated Objects wont help you, the SQL Code is bound to the object.

Ikrischer


#4

OK Ilja,

please, send a sample code showing which part you would like to have somewhere else and where you would like to have it.

Regards,
Lukas


#5

Hello Lukas,

– Table Trans_MediaCode

CREATE TABLE [Trans_MediaCode]
(
[PK_Trans_MediaCode] Bigint IDENTITY NOT NULL,
[MediaCode] Varchar(128) NOT NULL,
[Product_ID] Int DEFAULT 0 NOT NULL
)
go

INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘BMBC’, 1);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘BMBCNM’, 2);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘BMCL’, 3);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘BMCLNM3’, 4);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘BMGSP’, 5);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘BMGSPNM’, 6);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘BMCLNM’, 7);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MVGL1’, 8);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MVGL1_RTL’, 9);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MVGL1_UNI’, 10);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MVGL1_OMB’, 11);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MVGL1_MD’, 16);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MLAT’, 19);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MLEKAT’, 20);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MAPSL1’, 21);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MGSSGC’, 22);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MGSSP’, 23);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MGSSPM’, 24);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MGSSDUG’, 25);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MGSS2490’, 26);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘MGSS7470’, 27);
INSERT INTO Trans_MediaCode (MediaCode, Product_ID) VALUES (‘GTAT’, 147);

– Add keys for table Trans_MediaCode

ALTER TABLE [Trans_MediaCode] ADD CONSTRAINT [PK_Trans_MediaCode] PRIMARY KEY NONCLUSTERED ([PK_Trans_MediaCode])
go

ALTER TABLE [Trans_MediaCode] ADD CONSTRAINT [Trans_MediaCode_Product_ID] UNIQUE ([Product_ID])
go

ALTER TABLE [Trans_MediaCode] ADD CONSTRAINT [Trans_MediaCode_MediaCode] UNIQUE ([MediaCode])
go

The problem is, the keys are coming after the “After Script”

IKrischer


#6

Hi Ilja,

got it.

  1. Either you can just change settings, as I wrote in my first post, under menu Model, select Order of Generated Objects. On bookmark Extension, left-click on value at Keys, press F2 (or wheel-click), roll-up menu activates and select Inside CREATE TABLE Statement.
  2. Or use our solution we use for our models, which is put all INSERT DML Statements into After script.

Hope that works for you :slight_smile:

Cheers,
Lukas


#7

Hello Lukas,

option 2. is not really working, the INSERT statements should be bound to the table, no table no INSERT Statements :wink:

option1. is working somehow, but not really the way I would like to have it. Would be better, if an “After Script” is executed after all table definitions, which includes keys statements for me…

Ikrischer


#8

Hi Ilja,

ok, it is clear now. You can make KEYs to be executed before INSERT statements, but INSERT statements will always be executed before TRIGGERS and INDEXES, there is no option to change it so far. But I am passing it to our Allmighty guys and they will find a way, if there is any, to make it behave the way you would like it.
And just to make it clear- as I wrote about option 2, which you stated to be of no use, I meant to put all the INSERT statements in general After script in Model Properties, not in the particular entity after script.
Cheers,
Lukas


#9

Ilja, I have just been advised that there is a cutom package, that may solve your demand on functionality. See http://modeling.inside.quest.com/entry.jspa?externalID=3213&categoryID=34
Let me know.

Regards,
Lukas