So I am trying to build a trigger to maintain data consistency between two tables. My ideal trigger should look something like this (it's probably not correct, I haven't been able to properly debug it yet):
CREATE OR REPLACE TRIGGER salesFragmentGreaterThan
AFTER INSERT OR UPDATE OR DELETE ON sales
FOR EACH ROW WHEN (sale_price >= 5000000)
DECLARE
BEGIN
IF INSERTING THEN
INSERT INTO greaterThanFiveMillionFragment
VALUES (sales_seq.curval, :NEW.sales_date, :NEW.sale_type, :NEW.reserved_price, :NEW.sale_price, :NEW.deposit,
:NEW.balance, :NEW.buyer_id, :NEW.property_id);
ELSEIF DELETING THEN
DELETE FROM greaterThanFiveMillionFragment
WHERE greaterThanFiveMillionFragment.sales_id = :new.sales_id;
ELSEIF UPDATING THEN
UPDATE greaterThanFiveMillionFragment
SET :old.sales_date = :new.sales_date, :old.sale_type = :new.sale_type, :old.reserved_price = :new.reserved_price, :old.sale_price = :new.sale_price,
:old.deposit = :new.deposit, :old.balance = :new.balance, :old.buyer_id = :new.buyer_id, :old.property_id = :new.property_id
WHERE :old.sales_id = :newsales_id;
END IF;
/
This throws a series of invalid sql statement errors. I began removing code to find out which part was causing this until I got down to this:
CREATE OR REPLACE TRIGGER salesFragmentGreaterThan
AFTER INSERT OR UPDATE OR DELETE ON sales
FOR EACH ROW
WHEN (new.sale_price >= 5000000)
DECLARE
BEGIN
select sales_id from sales;
END;
/
This throws an invalid sql error. The only way I can get a trigger to compile is if there is literally nothing inside the BEGIN - END block.
What am I doing wrong?
This question was marked as a duplicate and I have been directed here: Declaring a variable and setting its value from a SELECT query in Oracle
I am afraid I don't understand how that question relaters to mine however. The issue I am having is that I can't seem to put any code inside my BEGIN block without the compiler declaring it as an invalid SQL statement.
