| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: trigger Problem
On Thu, 08 Jul 1999 08:03:00 -0700, Glen Upreti <Glen.Upreti_at_nau.edu> wrote:
>Hello People,
>I am writing a trigger on a table that is a security table for a vendor
>supplied package, the table looks like this:
>
>desc SEC_TABLE
>Column Name Null? Type
>------------------------------ -------- ----
>USERID NOT NULL VARCHAR2(8)
>VERSION NOT NULL NUMBER(38)
>
>and after deleting a user from the application it inserts the userid
>that was just deleted. At this point (after insert) I want to lock the
>account of the userid that was just deleted from the app. So I wrote
>the following trigger, making sure that the user doing the insert (lets
>call it BOB) was the owner of the trigger and explicitly (not through a
>role had ALTER USER. Here is BOB's trigger:
>
>CREATE OR REPLACE TRIGGER LOCKER
> AFTER INSERT ON BOB.SEC_TABLE
>FOR EACH ROW
>DECLARE
>cnum INTEGER;
>
>BEGIN
> cnum := SYS.DBMS_SQL.OPEN_CURSOR;
> SYS.DBMS_SQL.PARSE(cnum,'ALTER USER '||:NEW.USERID||' ACCOUNT
>LOCK',SYS.DBMS_SQL.NATIVE);
> SYS.DBMS_SQL.CLOSE_CURSOR(cnum);
> EXCEPTION
> WHEN OTHERS THEN
> SYS.DBMS_SQL.CLOSE_CURSOR(cnum);
>END LOCKER;
>/
>Since this did not work, I created a stored proc that had a hard coded
>userid in it, and that worked fine when I executed it. This seems like
>I am missing something very simple, like you can't use dbms_sql for ddl
>in a trigger or something. I also wrote a stored proc that takes an in
Exactly correct!
>of userid and locks the users account (this works by itself), but when
>it is called by the trigger it fails to lock the user's account!
dbms_sql can not be called in a trigger. Trigger's cannot commit or rollback and dbms_sql can issue DDL that will cause commits. Since it is impossible for the database to determine whether or not your dbms_sql will cause such a situation, no dbms_sql is allowed.
But fear not, before 8i you can still get around this by having the trigger schedule a dbms_job to run the DDL. So the users account will not be locked immediately when the update is done but when the job is run.
In 8i you could make your procedure run as an autonomous transaction which could then be called from the trigger.
hope this helps.
chris.
>Any help is appreciated,
>Glen
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |