Trigger Compiles but is invalid 2

[stuckDuck]

My trigger compliles but is invalid. Help appreciated.


For each user_id I want to generate a random pasword.
dbms_random.value is a system package that generates random characters.

**************

create or replace trigger trigger_password_generate
after insert or update of "USER_ID" on application_user
for each row
BEGIN

insert into application_user ("password")
select dbms_random.script('p',8) value from dual;

end;
/

Error Message:
Line 4, Position 21LS-00302: component 'SCRIPT' must be declared.

Line 4, Position 9: PL/SQL: ORA-00904: "DBMS_RANDOM"."SCRIPT": invalid identifier

Line 3, Postition 2: PL/SQL: SQL Statement ignored

 

[Turkbear]

Hi,
Why are you calling select dbms_random.script?

Shouldn't it be:
select dbms_random.string('p',8) from dual;






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[LKBrwnDBA]

Also, you should read about triggers in the fine Oracle® Database SQL Reference.

The way you have coded your trigger will generate an "ORA-04091: table name is mutating, trigger/function may not see it." error.




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb