Re: What is wrong with this trigger code

You can't just specify a cursor in your insert statement, you need the column
try

INSERT INTO messages(emp_emp_id,message,activate_dttime) VALUES ( c_emp.EMP_ID ,'Account '||acct_id||' has a bad address',SYSDATE);

--
Paul Dorsey
Dulcian, Inc.
www.dulcian.com
212 595 7223
P.S.
If you would like to talk to Dulcian about a salaried position on our team,
please call me.
We have openings in NJ for developers, in CA for PERL developers.

Brian Howard wrote in message <37B48987.97C97E64_at_creditwatch-inc.com>...


>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.

>

>

>