-- Account(AccountId, Customer, Balance)
-- AccountBackupImm / AccountBackupDef
-- DeltaPlusAccount / DeltaMinusAccount

CREATE TRIGGER InsAccount
AFTER INSERT ON Account
FOR EACH STATEMENT -- can be omissed, it's the default
BEGIN
    INSERT INTO AccountBackupImm
    SELECT * FROM NEW TABLE;

    INSERT INTO DeltaPlusAccount
    SELECT * FROM NEW TABLE;
END;

CREATE TRIGGER UpdAccount
AFTER UPDATE ON Account
FOR EACH STATEMENT
BEGIN
    -- We could use UPDATE, but just deleting old values
    -- and re-inserting them is easier
    DELETE FROM AccountBackupImm
    WHERE AccountId IN (SELECT AccountId FROM OLD TABLE);

    INSERT INTO AccountBackupImm
    SELECT *
    FROM NEW TABLE;

    INSERT INTO DeltaPlusAccount
    SELECT * FROM NEW TABLE;

    INSERT INTO DeltaMinusAccount
    SELECT * FROM OLD TABLE;
END;

CREATE TRIGGER DelAccount
AFTER DELETE ON Account
FOR EACH STATEMENT
BEGIN
    DELETE FROM AccountBackupImm
    WHERE AccountId IN (SELECT AccountId FROM OLD TABLE);

    INSERT INTO DeltaMinusAccount
    SELECT * FROM OLD TABLE;
END;

-- The SQL required to report data into AccountBackupDef
DELETE FROM AccountBackupDef
WHERE AccountId IN (SELECT AccountId FROM DeltaMinusAccount);

INSERT INTO AccountBackupDef
SELECT * FROM DeltaPlusAccount;

-- For "Ingegneria Informatica": tuples in Trans are already deleated automatically
-- We just have to do a DELETE on TransBackupImm when we do a DELETE on Account
-- The following code will be put into the previous triggers whenever we delete
-- an account. NOTE: we do not have to delete stuff when we do the update, only
-- on the DELETE
-- DELETE FROM TransBackupImm
-- WHERE AccountId IN (SELECT AccountId FROM OLD TABLE);
