I have created a table that houses four fields:

Project Number
Date
Time
Status

I am trying to write a trigger that will populate the above table when the status of a project changes.

What is the code to insert in one table when another (different) table is updated?

This is the error I currently receive:

------ Log Fatal SQL Statement -----
User : ericdev (pid 23963)
Date : 2002-04-23[14:21:52]
SQL :
UPDATE /*+ index(a ttipcs020795$idx1) */ baan.ttipcs020795 a SET t$psts=:1 WHERE t$cprj=:2


This is the current code:

CREATE OR REPLACE TRIGGER CPRJSTAT
BEFORE UPDATE ON ttipcs020795
FOR EACH ROW
WHEN (new.t$psts <> old.t$psts)
DECLARE
ws_cprj char(6);
ws_date date;
ws_time number(5,0);
ws_stat char(1);
BEGIN
ws_date := to_date(sysdate, 'DD-MON-RR');
ws_time := ((((to_number(sysdate,'HH') * 60) + to_number(sysdate,'MI')) * 60) + to_number(sysdate,'SS'));
ws_cprj := :old.t$cprj;
ws_stat := :new.t$psts;

INSERT INTO ttfobg950795 (t$cprj, t$date, t$time, t$stat)
VALUES (ws_cprj, ws_date, ws_time, ws_stat);
END;

/



Thanks in advance for any help.

Looks like t$psts is an Enum (long)? Did you use the same domain for ttipcs020795.psts and ttfobg950795.stat fields?

If so, try:
INSERT INTO ttfobg950795 (t$cprj, t$date, t$time, t$stat)
VALUES (ws_cprj, ws_date, ws_time, :new.t$psts);

Hi,

let us know, which error you are received in the alert-file. (Seems, thats an Oracle DB).

btw : why you are using an 'before update' trigger ??


Martin

It is also recommended, by Oracle, that all your SQL code be first created in an Oracle procedure. Then have the trigger call the procedure and pass any arguments necessary. The reasoning is that every time you call a trigger that has PL/SQL instructions coded into then requires Oracle to reprocess that code. But, by calling a pre-compiled procedure, you optimize the trigger by not having to make it spend more time processing it. Bottom line, your system has less work to do. Another benefit to putting your code in procedures is that they can be "pinned" into memory.

I also am wondering why you chose to do a "before" rather than an "after" trigger? In the case of a "before" trigger, it will always insert to the other table whether the triggering table commits (saves), or just does a rollback (undo).

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Thank you for all of the quick responses!

To answer gfasbender's question:

Yes, both fields have the same domain tcpsts.

To answer Martin's question:

The BW Message that pops up is:

Error 512 (Corrupted data dictionary) on tipcs020795 in db_update(51)
Cannot continue in tipcs2102m000 in DLL: ottstpstandard()
tipcs2102m000: A not terminated transaction is aborted

as far as Martin's btw and ggymer's questions:

This is my first trigger ever, and I don't directly work with Oracle (SQL) very much at all. I'm a finance guy (born and raised) that had the opportunity to transfer from the dulldrum's of our Finance department into the exciting world of IT (which I love) to support Finance from the IT side. I've been in IT for approx 6 month's so this is very new to me.

Please type your posts slowly as it takes me a while to read and understand .:D

If I can get the code working as is I will definately check out converting it to an Oracle procedure.

Thanks again!

One other pitfall in dealing directly with Baan tables through Oracle is dates. Oracle's date structure includes not only the date, but also the time. Baan does not like dates with times other than 12:00:00 AM. So, whenever you insert system dates into a Baan/Oracle table date column, always use TRUNC(SYSDATE) because it formats the time portion of the date correctly for what Baan wants. Basically it defaults it to 12:00:00 AM otherwise you end up with the current time as well as the current date. You don't need to use that TO_DATE and format mask on SYSDATE because SYSDATE is a vaid date value. The TO_DATE is for converting dates to various readable date string values.

Why do I know this? I found out the hard way since I have coded several Oracle triggers on Baan/Oracle tables to populate other Baan/Oracle tables.

Incorrect:
ws_date := to_date(sysdate, 'DD-MON-RR');

Correct:
ws_date := trunc(sysdate);

or use it directly in the INSERT

INSERT INTO ttfobg950795 (t$cprj, t$date, t$time, t$stat)
VALUES (ws_cprj, trunc(sysdate), ws_time, ws_stat);
END;

It also looks like you are trying to get the time in total seconds past midnight. This will give you the same thing. Try it out.

ws_time := TO_NUMBER(TO_CHAR(SYSDATE,'SSSSS'));

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Thanks for the tips, I am much closer!

I was able to get the trigger to work correctly, but now I need to add a record ID. I have added a new field to my table tfobg950795.idno.

I can write a select statement to count the number of records in my new table, but how do I assign that value to a variable?

I'd like to count the current number of records, assign that value to a variable, add 1 and then use that as my next idno when I update the table.

There are two ways you could approach this. You could create an Oracle sequence. A sequence is a number generator that can increment, start at, and go until any range of numeric values you want. The sequence keeps track of the last number used and automatically increments itself when called for the next logical number in the defined sequence. TTFOBG950795_SEQ is the name of the sequence (it can be named whatever you want) and NEXTVAL is the way that you invoke the next logical value. It can be assigned to a variable or directly used in the insert. I prefer the sequence because it is cleaner.

TTFOBG950795_SEQ.NEXTVAL

The other way is to get the MAX number of rows plus one from the table you are inserting to.
next_rec NUMBER;

SELECT MAX(*) + 1
INTO next_rec
FROM TTFOBG950795;

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Thanks for all the help it is greatly appreciated! Everything works perfectly.

My trigger is operating when tipcs020.psts changes. If someone modifies the tipcs020 table and then converts to runtime dictionary the trigger needs to be compiled again. Is there a way to avoid this or is there something that can be done to notify us if the trigger is not active?

When you change a table, in Baan, it unloads the table, drops the table and associated index(es) in Oracle, re-creates them back in Oracle, and then reloads them. Because the table got dropped, it hosed the trigger which required you to re-compile it.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

I guess I could create a batchjob that runs in the morning to re-compile the trigger. That could minimize the data loss.