Question

I have this trigger

procedure CASE_A  
as  
UPDATE table1 SET field1 = (SELECT bus FROM view1 WHERE table1.document = view1.document)  
end;

the below trigger is supposed to execute the above procedure CASE_A (Case_A is supposed to put a value into field1)

CREATE OR REPLACE TRIGGER "CASE_A" AFTER INSERT ON "TABLE1" FOR EACH ROW  
BEGIN  
CASE_A;
END;

but, it s not doing that.

Answer 1

you are doing it wrong, at least assuming this is Oracle.

better do it like this:

CREATE OR REPLACE TRIGGER "CASE_A" BEFORE INSERT ON "TABLE1" FOR EACH ROW  
BEGIN  
    SELECT bus INTO :new.field1 FROM view1 WHERE :new.document = view1.document;
END;

EDIT: since view1 depends on table1, we need to do a bit more... (read comments for more information)

CREATE OR REPLACE TRIGGER "CASE_A" BEFORE INSERT ON "TABLE1" FOR EACH ROW  
BEGIN  
    SELECT sum(FUNCTION_CASEA(b.DUE, b.RETD)) INTO :new.field1
      FROM table2
      WHERE table1.document = table2.document 
        AND table1.field1 = table2.branch;
END;

友情链接