Re: Help - Database Trigger

From: Viktor Naumov <stant_98_at_yahoo.com>
Date: Wed, 17 May 2000 15:33:32 -0700 (PDT)
Message-Id: <10500.105901@fatcity.com>

--0-1649760492-958602812=:9977
Content-Type: text/plain; charset=us-ascii Content-Disposition: inline

see attached file.
Hope it helps.
--- salu Ullah <salu_ullah_at_hotmail.com> wrote:
> Hello,
>
> Iam trying to create a database trigger that should
> fire & delete a row
> after all the columns are made null except the
> foreign key which is also a
> primary key of that table.
> I have a table that has rate_cd column as a primary
> key & five more column
> as(rate_alt1, rate_alt2.... rate_alt5)
> Now, if i make all the alternate rates null, it
> should delete the row
> completely.
> I have tried & created an after insert or upate
> trigger but its giving me
> mutating error
> because its quering its own triggering table.
> Any workaround for the mutating table error??
>
> Thanks in advance
>
> Salu
>
>
>

---

> Get Your Private, Free E-mail from MSN Hotmail at
> http://www.hotmail.com
>
> --
> Author: salu Ullah
> INET: salu_ullah_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>

---

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>

---

Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger. http://im.yahoo.com/
--0-1649760492-958602812=:9977

Content-Type: text/plain; name="trigger_plsql_package.txt"
Content-Description: trigger_plsql_package.txt
Content-Disposition: inline; filename="trigger_plsql_package.txt"

create or replace package plstab as
TYPE saltab is table of emp.sal%type INDEX BY BINARY_INTEGER; TYPE mgrtab is table of emp.mgr%type INDEX BY BINARY_INTEGER; v_saltab saltab;
v_mgrtab mgrtab;
no_of_rows number :=0;
end plstab;
/
create or replace trigger emp_update_before before update of sal on emp for each row begin

plstab.no_of_rows :=plstab.no_of_rows + 1;
plstab.v_saltab(plstab.no_of_rows) :=:new.sal;
plstab.v_mgrtab(plstab.no_of_rows) :=:new.mgr;

end;
/
create or replace trigger emp_update_after after update on emp declare
mgr_sal emp.mgr%type;
n emp.mgr%type;
m emp.sal%type;
begin
for i in 1..plstab.no_of_rows
loop
m:=plstab.v_saltab(i);
n:=plstab.v_mgrtab(i);
select sal into mgr_sal from emp where empno=n; if m>mgr_sal then
raise_application_error(-20004,'Employee salary cannot be more than his manager'); Received on Wed May 17 2000 - 17:33:32 CDT