Triggers

[MizzGail]

I have never written a trigger before and I'm having problems following these wonderful manuals I have.

What I want to do is this:
On a daily basis create two new tables
create table tableA (fld1 char)
create table tableB (fld1 char)

Then I need to track new records or updates seperately.

create trigger newrecord insert on table1
for each row(insert keyfields from table1 into tableA)


create trigger update1 update of qty on table1
for each row (insert keyfields from table1 into tableB)


I'm getting syntax errors. How do I get the keyfield from table1 for each new record or updated records ?

 

[olded]

MizzGail:

Given: tablea & table1 are the same structure with one column fld1 char(1).

Here's the insert trigger:

create trigger ti_table1 insert on table1
referencing new as new_record for each row
(
insert into tablea values (new_record.fld1)
);

You seem to be forgetting the referencing new string and the beginning and ending parenthesis.

I'm concerned about your comment:

-On a daily basis create two new tables

If you are dropping the two tables and recreating them daily, you'll have to recreate the triggers since they go away when you drop the table.

Regards,

Ed

 

[MizzGail]

table1 will always be resident. It is part of our ERP system.

I need to extract updates and inserts to be extracted via another process and sent to another system. I only need the key information for this other session to drive the extracts.

I will be recreating the tables that hold the extract key values due to space constraints. Most likely, since we are a 24x7 system, it will happen on sunday once a month during system maintenance. Does it make a difference if I am only keeping one fields on the tracking tables ?

 

[olded]

MizzGail:

I'd say you're on the right track. It makes sense to only keep the data that's required.

Regards,

Ed