Home » SQL & PL/SQL » SQL & PL/SQL » Calling Procedure from Trigger
Calling Procedure from Trigger [message #178965] Fri, 23 June 2006 11:16 Go to next message
kumpfdp
Messages: 10
Registered: June 2006
Junior Member
What's the syntax for calling a procedure from a trigger? I need to pass a value to that procedure as well...

Thanks!
Re: Calling Procedure from Trigger [message #178968 is a reply to message #178965] Fri, 23 June 2006 12:17 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
.
.
.
call procedure_name(:new.param1, :new.param2, etc.);
.
.
.


updated to show how to use current row values.

[Updated on: Fri, 23 June 2006 12:21]

Report message to a moderator

Re: Calling Procedure from Trigger [message #178969 is a reply to message #178965] Fri, 23 June 2006 12:28 Go to previous messageGo to next message
kumpfdp
Messages: 10
Registered: June 2006
Junior Member
Is it possible to have the procedure being called from trigger delete from the table that the trigger is firing from?
Re: Calling Procedure from Trigger [message #178970 is a reply to message #178965] Fri, 23 June 2006 12:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is it possible to have the procedure being called from trigger delete from the table that the trigger is firing from?
NO, the trigger can NOT change the table to which is "attached".
Re: Calling Procedure from Trigger [message #178971 is a reply to message #178965] Fri, 23 June 2006 12:59 Go to previous messageGo to next message
kumpfdp
Messages: 10
Registered: June 2006
Junior Member
I've done it for one trigger, but was unsuccessful in trying to duplicate what i had done with this one...
Re: Calling Procedure from Trigger [message #178981 is a reply to message #178971] Fri, 23 June 2006 13:44 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I don't believe you.
Re: Calling Procedure from Trigger [message #178986 is a reply to message #178981] Fri, 23 June 2006 14:37 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Careful Joy!
SQL> create table faq
  2  ( id number)
  3  /

Table created.

SQL> create or replace procedure del_faq
  2  is
  3  begin
  4    delete faq;
  5    dbms_output.put_line(sql%rowcount||' records deleted');
  6  end;
  7  /

Procedure created.

SQL> create or replace trigger faq_aus
  2  after update on faq
  3  begin
  4    del_faq;
  5  end;
  6  /

Trigger created.

SQL> 
SQL> insert into faq (id) values (1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> update faq set id = 2 where rownum = 1;
3 records deleted

1 row updated.

[Updated on: Fri, 23 June 2006 14:37]

Report message to a moderator

Re: Calling Procedure from Trigger [message #178989 is a reply to message #178986] Fri, 23 June 2006 15:08 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Thanks Frank. My fault.

What I should have clarified was in refernce to the statement:
Quote:

"I've done it for one trigger, but was unsuccessful in trying to duplicate what i had done with this one..."


I was saying I didn't believe the OP because if OP was able to do it for one trigger, why were they not able to do it with their current trigger? No code was posted so I was kind of pushing to see how they did it with their first trigger and how they are doing it with the second triger so that someone can point out the difference.

I was sloppy.
Re: Calling Procedure from Trigger [message #178992 is a reply to message #178989] Fri, 23 June 2006 15:44 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I knew what you were after. I am all with you / with your intentions. Just couldn't resist Cool
Re: Calling Procedure from Trigger [message #179242 is a reply to message #178965] Mon, 26 June 2006 08:15 Go to previous message
kumpfdp
Messages: 10
Registered: June 2006
Junior Member
I'm not sure why i couldn't get it to work the second time, could be something with the difference in tables, I'm really not sure...

I do know that the second modification I was trying, I was having problems with the cursor...it kept sending me an error when i inserted into the table saying 'Invalid Cursor'. I'm looking into why with the one trigger it creates perfectly fine and works as needed and the other doesn't. I will post what I find out, when i find it out... Wink

oh, and here is the code that works:

<code>
CREATE OR REPLACE TRIGGER stmdocheadlinetrig
AFTER
INSERT
ON <Table>
DECLARE
l_stmcode <TABLE>.stmcode%TYPE;
l_stmnum .stmnum%TYPE;
l_cmpcode .cmpcode%TYPE;
l_doccode .doccode%TYPE;
l_docnum .docnum%TYPE;
--
l_version .version%TYPE;
l_yr .yr%TYPE;
l_period .period%TYPE;
l_curdoc .curdoc%TYPE;
l_docdate .docdate%TYPE;
l_authuser .authuser%TYPE;
--
l_doclinenum .doclinenum%TYPE;
l_valuestm .valuestm%TYPE;
l_valuestm_dp .valuestm_dp%TYPE;
l_trancode .trancode%TYPE;
l_acclookup .acclookup%TYPE;
l_srcorcontra .srcorcontra%TYPE;
l_statrecint .statrecint%TYPE;
l_statrec .statrec%TYPE;
l_accode .accode%TYPE;
l_valuedoc .valuedoc%TYPE;
l_valuedoc_dp .valuedoc_dp%TYPE;
l_docrate .docrate%TYPE;
l_valuehome .valuehome%TYPE;
l_valuehome_dp .valuehome_dp%TYPE;
l_valuedual .valuedual%TYPE;
l_valuedual_dp .valuedual_dp%TYPE;
l_dualrate .dualrate%TYPE;
l_statuser .statuser%TYPE;
l_linetype .linetype%TYPE;
l_debitcredit .debitcredit%TYPE;
l_duedate .duedate%TYPE;
l_valdate .valdate%TYPE;
l_taxcode9 .taxcode9%TYPE;
l_taxvalue9 .taxvalue9%TYPE;
l_taxvalue9_dp .taxvalue9_dp%TYPE;
l_descr .descr%TYPE;
l_extref6 .extref6%TYPE;
l_eclurr8 .eclurr8%TYPE;
l_elvalue8 .elvalue8%TYPE;
l_elvalue8_dp .elvalue8_dp%TYPE;
l_elrate8 .elrate8%TYPE;
l_discsflag .discsflag%TYPE;
l_discdate5 .discdate5%TYPE;
l_discrate5 .discrate5%TYPE;
l_discvalue5 .discvalue5%TYPE;
l_discvalue5_dp .discvalue5_dp%TYPE;
l_docsumtax .docsumtax%TYPE;
l_docsumtax_dp .docsumtax_dp%TYPE;
l_taxlinecode .taxlinecode%TYPE;
l_doctaxturn .doctaxturn%TYPE;
l_doctaxturn_dp .doctaxturn_dp%TYPE;
l_ten99taxcode3 .ten99taxcode3%TYPE;
l_ten99taxvalue3 .ten99taxvalue3%TYPE;
l_ten99taxvalue3_dp .ten99taxvalue3_dp%TYPE;
l_medcode .medcode%TYPE;
l_bnkcode .bnkcode%TYPE;
l_elmbanktag .elmbanktag%TYPE;
l_elmaddrtag .elmaddrtag%TYPE;
l_usrref3 .usrref3%TYPE;
l_tradercode .tradercode%TYPE;
l_posted .posted%TYPE;
l_lineseqno .lineseqno%TYPE;
l_actdoccode .actdoccode%TYPE;
l_actdocnum .actdocnum%TYPE;
l_actdocnlinenum .actdoclinenum%TYPE;
l_accode_offset .accode_offset%TYPE;
l_debitcredit_offset .debitcredit_offset%TYPE;
--
l_docnum_new .docnum%TYPE;
l_count PLS_INTEGER;
l_rowid ROWID;
CURSOR C_STMDOCHEADLINE
IS
SELECT stmcode
, stmnum
, cmpcode
, doccode
, docnum
--
-- head columns
--
, version
, yr
, period
, curdoc
, docdate
, authuser
--
-- line columns
--
, doclinenum
, valuestm
, valuestm_dp
, trancode
, acclookup
, srcorcontra
, statrecint
, statrec
, accode
, valuedoc
, valuedoc_dp
, docrate
, valuehome
, valuehome_dp
, valuedual
, valuedual_dp
, dualrate
, statuser
, linetype
, debitcredit
, duedate
, valdate
, taxcode9
, taxvalue9
, taxvalue9_dp
, descr
, extref6
, eclurr8
, elvalue8
, elvalue8_dp
, elrate8
, discsflag
, discdate5
, discrate5
, discvalue5
, discvalue5_dp
, docsumtax
, docsumtax_dp
, taxlinecode
, doctaxturn
, doctaxturn_dp
, ten99taxcode3
, ten99taxvalue3
, ten99taxvalue3_dp
, medcode
, bnkcode
, elmbanktag
, elmaddrtag
, usrref3
, tradercode
, posted
, lineseqno
, actdoccode
, actdocnum
, actdoclinenum
--
-- offsetting columns
--
, accode_offset
, debitcredit_offset
--
FROM <TABLE>
ORDER BY docnum, accode
FOR UPDATE;
--
BEGIN
--
-- This trigger takes the individual headline rows and moves them to
-- the oas_stmdochead and oas_stmdocline tables
--
-- For oas_stmdochead:
-- 1) If an oas_stmdochead exists with the same head column values then we will add to it
-- otherwise create a new oas_stmdochead with similar docnum
--
-- For oas_stmdocline:
-- 1) If an oas_stmdocline exists with the same line column values then we will add to them
-- otherwise create a new oas_stmdocline
-- 2) For offset account, no descriptive or ref column matching is done
--
OPEN C_STMDOCHEADLINE;
LOOP
FETCH C_STMDOCHEADLINE
INTO l_stmcode
, l_stmnum
, l_cmpcode
, l_doccode
, l_docnum
--
, l_version
, l_yr
, l_period
, l_curdoc
, l_docdate
, l_authuser
--
, l_doclinenum
, l_valuestm
, l_valuestm_dp
, l_trancode
, l_acclookup
, l_srcorcontra
, l_statrecint
, l_statrec
, l_accode
, l_valuedoc
, l_valuedoc_dp
, l_docrate
, l_valuehome
, l_valuehome_dp
, l_valuedual
, l_valuedual_dp
, l_dualrate
, l_statuser
, l_linetype
, l_debitcredit
, l_duedate
, l_valdate
, l_taxcode9
, l_taxvalue9
, l_taxvalue9_dp
, l_descr
, l_extref6
, l_eclurr8
, l_elvalue8
, l_elvalue8_dp
, l_elrate8
, l_discsflag
, l_discdate5
, l_discrate5
, l_discvalue5
, l_discvalue5_dp
, l_docsumtax
, l_docsumtax_dp
, l_taxlinecode
, l_doctaxturn
, l_doctaxturn_dp
, l_ten99taxcode3
, l_ten99taxvalue3
, l_ten99taxvalue3_dp
, l_medcode
, l_bnkcode
, l_elmbanktag
, l_elmaddrtag
, l_usrref3
, l_tradercode
, l_posted
, l_lineseqno
, l_actdoccode
, l_actdocnum
, l_actdocnlinenum
--
, l_accode_offset
, l_debitcredit_offset;
EXIT WHEN C_STMDOCHEADLINE%NOTFOUND;
BEGIN
begin
SELECT docnum
INTO l_docnum_new
FROM oas_stmdocline
WHERE stmcode = l_stmcode
AND stmnum = l_stmnum
AND cmpcode = l_cmpcode
AND doccode = l_doccode
AND docnum = l_docnum
AND (yr = l_yr OR (yr IS NULL and l_yr IS NULL))
AND (period = l_period OR (period IS NULL and l_period IS NULL))
AND (curdoc = l_curdoc OR (curdoc IS NULL and l_curdoc IS NULL))
AND (docdate = l_docdate OR (docdate IS NULL and l_docdate IS NULL));
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT MAX(docnum)
INTO l_docnum_new
FROM oas_stmdocline
WHERE stmcode = l_stmcode
AND stmnum = l_stmnum
AND cmpcode = l_cmpcode
AND doccode = l_doccode
AND (docnum like l_docnum || '%')
AND (yr = l_yr OR (yr IS NULL and l_yr IS NULL))
AND (period = l_period OR (period IS NULL and l_period IS NULL))
AND (curdoc = l_curdoc OR (curdoc IS NULL and l_curdoc IS NULL))
AND (docdate = l_docdate OR (docdate IS NULL and l_docdate IS NULL));
end;
IF l_docnum_new IS NULL THEN
SELECT MAX(docnum)
INTO l_docnum_new
FROM oas_stmdochead
WHERE stmcode = l_stmcode
AND stmnum = l_stmnum
AND cmpcode = l_cmpcode
AND doccode = l_doccode
AND (docnum like l_docnum || '%');
--
-- Determine new docnum
--
IF l_docnum_new IS NULL THEN
l_docnum_new := l_docnum;
ELSE
IF l_docnum_new = l_docnum THEN
l_docnum_new := l_docnum || '0';
END IF;
BEGIN
SELECT l_docnum || TO_CHAR(TO_NUMBER(SUBSTR(l_docnum_new,LENGTH(l_docnum)+1))+1)
INTO l_docnum_new
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
l_docnum_new := l_docnum_new || '1';
END;
END IF;
--
-- insert new oas_stmdochead
--
INSERT INTO oas_stmdochead
( stmcode
, stmnum
, cmpcode
, doccode
, docnum
, yr
, period
, curdoc
, docdate
) VALUES (
l_stmcode
, l_stmnum
, l_cmpcode
, l_doccode
, l_docnum_new
, l_yr
, l_period
, l_curdoc
, l_docdate
);
END IF; -- IF no unposted similar docnum
END;
-- do offset first
l_count := 0;
SELECT NVL(COUNT(*),0), MIN(ROWID)
INTO l_count, l_rowid
FROM oas_stmdocline
WHERE stmcode = l_stmcode
AND stmnum = l_stmnum
AND cmpcode = l_cmpcode
AND doccode = l_doccode
AND docnum = l_docnum_new
AND accode = l_accode_offset -- offsetting entry
AND valuedoc_dp = l_valuedoc_dp
AND linetype = l_linetype
AND debitcredit = l_debitcredit_offset -- offsetting entry
AND descr IS NULL
AND extref6 IS NULL;
IF l_count > 0 THEN
UPDATE oas_stmdocline
SET valuedoc = valuedoc + l_valuedoc
WHERE ROWID = l_rowid;
ELSE
l_count := 0;
LOOP
BEGIN
INSERT INTO oas_stmdocline
( stmcode
, stmnum
, cmpcode
, doccode
, docnum
, posted
, doclinenum
, accode
, valuedoc
, valuedoc_dp
, linetype
, debitcredit
, trancode
, valuestm
, valuestm_dp
, srcorcontra
) VALUES (
l_stmcode
, l_stmnum
, l_cmpcode
, l_doccode
, l_docnum_new
, l_posted
, (SELECT NVL(MAX(doclinenum),0)+1
FROM oas_stmdocline
WHERE stmcode = l_stmcode
AND stmnum = l_stmnum
AND cmpcode = l_cmpcode
AND doccode = l_doccode
AND docnum = l_docnum_new)
, l_accode_offset
, l_valuedoc
, l_valuedoc_dp
, l_linetype
, l_debitcredit_offset
, l_trancode
, l_valuestm
, l_valuestm_dp
, l_srcorcontra
);
l_count := -1; -- set the count to -1 so that we can exit
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
l_count := l_count + 1;
IF l_count > 100 THEN
RAISE; -- re-raise the duplicate error if can't get it in 100 tries
END IF;
END;
EXIT WHEN l_count = -1; -- exit the loop if insert was OK
END LOOP;
END IF;
--
END LOOP;
END IF;
--
DELETE stmdocheadline WHERE CURRENT OF C_STMDOCHEADLINE;
--
END LOOP;
CLOSE C_STMDOCHEADLINE;
--
END;
</code>
Previous Topic: SQL standards??
Next Topic: v$session Query
Goto Forum:
  


Current Time: Fri Mar 29 06:29:27 CDT 2024