Create database Trigger Before/After and get the inserted data

[TSeegers]

Hello,

I have a problem and I hope that someone can help me.
im using Progress Openedge 10.1a
this is the situation:
-I want to set up a trigger on a table so when someone adds a record i can manipulate the inserted data (I need a Before insert trigger right? )
-I found out that I can't set up a before insert trigger with the data dictionary.(correct me if I'm wrong)
-Even if i set up a Write trigger I still can't get to the user inserted data.(if this is possible tell me how pls)

I'm banging my head against the wall here so i tried the following:
- I've set up an odbc driver so I can get to the database inside "Squirrel SQL"(=sql explorer tool)
- Inside this tool I can set up a before insert trigger like this:
CREATE TRIGGER test
BEFORE INSERT ON pub.personen FOR EACH ROW
BEGIN
''
END
-But when I set something between the begin & end I get a error(a trigger with no content is worthless)

So my questions are:
-Can I set up a Before Trigger on a table with a database from Progress 10.1a?
-Can I get the user input so I can manipulate data inside a Trigger?
-Is it possible to set up a Trigger inside a explorer-tool connected to my Progress 10.1a database?

I hope you understand my problem and that you have a solution.

 

[RealHeavyDude]

IMHO, it is bad design to hide business logic in database triggers.

Your problem is that the 4GL and SQL database are different worlds that only share a few things. The data dictionary basically belongs to the 4GL world and the database triggers you define inhere can only be executed by 4GL clients. Yes, the 4GL database triggers are executed by the 4GL client and not the database engine. These 4GL clients do not understand SQL syntax.

On the other side, the 4GL world is totally oblivious about SQL table triggers as these can only be executed on the SQL database engine.

From my experience it's hardly possible to have a common set of business logic packed in database triggers which are to be executed by 4GL as well as SQL.

One solution, and IMHO the best, is to have the clients access the AppServer which will then execute the business logic no matter what the client technology is. Another solution, if it's for pure auditing purposes would be the built in auditing functionality as of the OE10.1A+ database.

Regards, RealHeavyDude.

 

[TSeegers]

Thanks for your reply RealHeavyDude.

AppServer is not a option duo the licence costs.
I know its not the perfect thing to do, but I want to give it a try....

after reading your reaction I still have one question(I'm sorry if I understood you wrong):
-Can I get the user input so I can manipulate data inside a Trigger?
In MS SQL I can manage that by something like this:
create trigger trg_test on mytest FOR update
as
begin
update summary
set trade_quantity = ( trade_quantity -
( select sum( trade_quantity )
from deleted ) )
+ ( select sum( trade_quantity )
from inserted )

end

where deleted = the old value and inserted = the users input value.
I hope you know what i mean.
so..
Can I get the user input so I can manipulate data inside a Trigger?

 

[RealHeavyDude]

Yes you can ...

In the write database trigger you could do something like this:

TRIGGER PROCEDURE FOR WRITE OF cfi_Assembly OLD BUFFER o_cfi_Assembly.

IF o_cfi_Assembly.assemblyDate <> cfi_Assembly.assemblyDate THEN DO:
/* Your stuff ... */
ASSING cfi_Assembly.assemblyIsOnHold = TRUE.

END.

Don't blame me for the silly code - it's just an example of what you can do in the trigger ...

Again, I am NOT in any way a fan of hiding business logic in database triggers - but that's another story.

Regards, RealHeavyDude.

 

[TSeegers]

thank you very much

 

[LarryD]

RHD, do you have something against the cfi_Assembly.assemblyIsOnHold field that you'd like to share? ;-)

 

[RealHeavyDude]

Quite frankly, exactly this code from an infamous developer caused me lots of headaches because it didn't make any sense .

Regards, RealHeavyDude.

 

[LarryD]

Okay.... I was just "ASSING" ;-)

 

[RealHeavyDude]

That's a typo. I typed the code OTOH ...

RealHeavyDude.