[Edje]

Here goes:

i have three tables, table1, table2, table3. They are al similar lets say:

create table<1-3>
(
col1 int,
col2 int);

Now that i got 3 tables.. i want only use table 1 for inserts update and deletes.

If i insert/update/ on table1 it will be triggerd to table2. Table3 will be used for archieving and should only be filled if a delete takes place.

There are no trigger problems when i insert or update on table1. The trigger to table2 works perfectly but if i delete a row on table1 it should be deleted on table2 but inserted on table3.

the function on table1 looks like this:

CREATE FUNCTION table1() RETURNS OPAQUE as '
BEGIN
INSERT INTO table2
values
(
NEW.col1,
NEW.col2
);
RETURN NULL;

END;
' language 'plpgsql';

and the trigger:

CREATE TRIGGER table1
AFTER INSERT or UPDATE
ON table1
FOR EACH ROW
EXECUTE PROCEDURE table1();

But i cant figure out how the function would look like if i wanted to perform a triggerd delelete on table2 from table1 wich would result in a inserted trigger to table3.

Perphaps its not even possible.. but it makes me head spin not knowing....

[eperich]

maybe you try it with rules

Create rule

so you cann define a rule on each of the three tables
and write

INSTEAD OF
INSERT
DO INSERT
INSERT
INSERT

so you have not a problem with triggers

[Edje]

Thanks for the idea.

I'll give it a shot.

-Ed

[Edje]

Well, i've managed to create what i was looking for (all updates/insert/deletes) on one table that will "copied" to another active table and upon delete will be copied to a "archive" table and clearing the other two tables.

If anyone is interested in the code..let me know an ill post it.

Thanks for pointing me into the right direction (rules).

-Ed

[AStefan]

If I understand correctly or if I not I would appreciate if you can advice me in the problem of using rules instead of "instead of insert/update/delete" on views.
If I have, for example three tables:
A (id, name)
B(fk1, name)
C(fk2, name)
And a view named V
create view V as
select * from A,B,C where A.id = B.fk1 and B.fk1 = C.fk2)

I would like to insert, update and delete in these three tables using the view. I cannot use instead of triggers because they are replaced with rules. But I don't know what is my mistake because I am informed that "ERROR: NEW used in query that is not in a rule" when I create the rule like that:

CREATE or replace RULE r AS ON INSERT TO v DO INSTEAD
INSERT INTO A (id, name) VALUES (NEW.id, NEW.name);
INSERT INTO B (fk1, name) VALUES (NEW.fk1, NEW.name);
INSERT INTO C (id, name) VALUES (NEW.fk2, NEW.name);

Can anyone tell me, please, in this example, how can I substitute an instead of view with a rule?
Thanks in advanced.