Re: What is wrong with this trigger code

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Mon, 16 Aug 1999 17:11:58 +0800
Message-ID: <37B7D5DE.502F@yahoo.com>

Brian Howard wrote:
>
> DECLARE
>
> v_coll accounts.coll_coll_id%TYPE;
>
> CURSOR c_emp IS SELECT EMPLOYEES.EMP_ID
> FROM ACCOUNTS, COLLECTORS, EMPLOYEES
> WHERE accounts.coll_coll_id = v_coll and
> ((accounts.coll_coll_id=collectors.coll_id)
> AND (collectors.emp_emp_id=employees.emp_id));
>
> BEGIN
> IF :old.aad_return = 'N' AND :new.aad_return = 'Y' THEN
> v_coll := :new.coll_coll_id;
> OPEN c_emp;
> INSERT INTO messages(emp_emp_id,message,activate_dttime)
> VALUES (c_emp,'Account '||acct_id||' has a bad address',SYSDATE);
> CLOSE c_emp;
> END IF;
> END;
>
> All this does is write a message to a messages table when the aad_return
> field is marked to 'Y' on the accounts table. When I try to generate it,
> I get the following message.
>
> CDI-11307 Warning: (compilation error in TRIGGER 'MAIL_RETURN' at 16/9):
> PLS-00320: the declaration of the type of this expression is incomplete
> or malformed
> CDI-11307 Warning: (compilation error in TRIGGER 'MAIL_RETURN' at 15/1):
> PL/SQL: SQL Statement ignored
>
> In order to get the employee_id, the linkage of tables is like this:
>
> Accounts(table)
> acct_id pk
> coll_coll_id fk
>
> Collectors(table)
> coll_id pk
> emp_emp_id fk
>
> Employees(table)
> emp_id pk
>
> HELP.
rather than

open
insert
close

you will need:

open
fetch into some variables
insert (those variables)
close

or re-code it as an insert-select without the cursor at all

Cheers
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Mon Aug 16 1999 - 04:11:58 CDT