If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Need help with dynamic SQL in trigger

I am trying to create a trigger that fires after a user logon and logoff and does the following:

creates a new user
deletes data from old temp table

Then I need to create a package that executes dynamically to
drop old users
remove the old user account access

We are running Oracle 8i/9iR2 on UNIX.
Since I am new to PL/SQL programming and Oracle could anyone help point me in the right direction? Can I write dynamic SQL in a trigger or package to do these things?

What problem are you really trying to solve?
On the surface the "design" sounds to be a total kludge.
>I am trying to create a trigger that fires after a user logon and logoff and does the following:
You need to make up your/our mind. LOGON<>LOGOFF
Fire for every USER which that does logon & logoff?
>creates a new user
Random name & password? WHY?
>Then I need to create a package that executes dynamically to drop old users
WHY?

You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.

logon trigger to detect changes to database

Actually what I need is to create a trigger to detect changes made by a user account after the user logs into the database and makes changes against the database. Sorry for the confusion. More of an auditing tool to track DDL changes made by a user.

You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Good judgement comes from experience. Experience comes from bad judgement.