OraFAQ Forum: SQL & PL/SQL » Re: want to stop the insert/delete/update transaction in an AFTER TRIGGER

Home » SQL & PL/SQL » SQL & PL/SQL » Re: want to stop the insert/delete/update transaction in an AFTER TRIGGER

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Re: want to stop the insert/delete/update transaction in an AFTER TRIGGER [message #39975]

Thu, 29 August 2002 12:22

Gaurang
Messages: 4
Registered: August 2002

Junior Member

consider this example:
there 3 tables customer(cust_no,c_name,balance)
deposit(cust_no,date,deposit_amount)
withdraw(cust_no,date,drawn_amount)
REQUIREMENTS: Balance should be minimum 5000
CAN YOU GIVE ME THE SOLUTION

problem is : trigger is created with compilation errors and i am not able to detect the errors in the following code.Please help me.

create trigger update_bal
before
insert or update of d_amount or delete on deposit
for each row
declare
bal customer.balance%type;
trans_error EXCEPTION;
begin
bal := :old.balance + :new.d_amount;
if bal >= 5000 then
if inserting then
update customer
set balance := bal
where cust_no = :new.cust_no;
elseif deleting then
update customer
set balance := balance - bal
where cust_no = :new.cust_no;
elseif updating then
update customer
set balance := bal
where cust_no = :new.cust_no;
else
RAISE trans_error;
end if;
end if;
Exeception
WHEN trans_error then
raise_application_error(-20026,'Transaction terminated as bal is < then 5000 ');
end;

Report message to a moderator

Re: want to stop the insert/delete/update transaction in an AFTER TRIGGER [message #39977 is a reply to message #39975]

Thu, 29 August 2002 14:37

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

1) I would think you would want both types of transactions in a single table with a transaction type (CR/DB). Separating tables by transaction type is making your queries and DML far more complicated than it should be.

2) There are many errors (syntatical and logical) with your trigger. Here is a suggested rewrite:

create or replace trigger update_bal
  before insert or update of d_amount or delete on deposit
  for each row
declare
  v_amount   customer.balance%type;
  v_cust_no  customer.cust_no%type;
  v_balance  customer.balance%type;
begin
  if inserting or updating then
 
    if inserting then
      v_amount := :new.d_amount;
    elsif updating then
      v_amount := :new.d_amount - :old.d_amount;
    end if;
 
    v_cust_no := :new.cust_no;
 
  elsif deleting then
 
    v_amount := -:old.d_amount;
    v_cust_no := :old.cust_no;
 
  end if;  
 
  update customer
     set balance := nvl(balance, 0) + v_amount
   where cust_no = v_cust_no
   returning balance into v_balance;
 
  if v_balance < 5000 then  
    raise_application_error(-20026, 'Transaction terminated as balance < 5000');
  end if;  
end;
/

Let me know any questions/comments you may have.

Report message to a moderator