CODEWITHSUNDEEP
databasetriggersdb2database-trigger
cildoz
cildoz

Reputation: 436

DB2 raise application trigger

I've defined this trigger

CREATE TRIGGER actualizarSaldoRetirada
BEFORE INSERT ON Retirada
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN
    IF (SELECT Saldo FROM Cuenta WHERE IBAN = N.Cuenta_IBAN) - N.Cantidad >= 0 THEN
        UPDATE Cuenta SET Saldo = Saldo - N.Cantidad WHERE IBAN = N.Cuenta_IBAN;
    ELSE
        RAISE_APPLICATION_ERROR(-20000, 'El saldo de la cuenta no puede ser negativo');
    END IF;
END@

But db2 returns the following error

An unexpected token "RAISE_APPLICATION_ERROR" was found following ".Cuenta_IBAN; ELSE "

Any ideas to solve it?

Upvotes: 1

Views: 234

Answers (1)

mao
mao

Reputation: 12267

the RAISE_APPLICATION_ERROR is available only in pl/sql contexts. Consider using SIGNAL instead.

For example (choose your own suitable SQLSTATE value from a valid range):

CREATE TRIGGER actualizarSaldoRetirada
BEFORE INSERT ON Retirada
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN 
    IF (select saldo from cuenta where iban = N.cuenta_iban  ) - N.Cantidad >= 0 THEN
        UPDATE Cuenta SET Saldo = Saldo - N.Cantidad WHERE IBAN = N.Cuenta_IBAN;
    ELSE
        SIGNAL SQLSTATE '75002' set message_text= 'El saldo de la cuenta no puede ser negativo';
    END IF;
END

Upvotes: 3

Related Questions