trigger Problem

From: Glen Upreti <Glen.Upreti_at_nau.edu>
Date: Thu, 08 Jul 1999 08:03:00 -0700
Message-ID: <3784BDA4.9845D1A3@nau.edu>

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 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! Any help is appreciated,
Glen
(Remove NOSPAM to reply).

PS what does HTH stand for?
--
|-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-| Glen Upreti
Senior Oracle DBA
Northern Arizona University
Phone (520)523-8393 Fax(520)523-7407 Glen.Upreti_at_NOSPAMnau.edu

Received on Thu Jul 08 1999 - 10:03:00 CDT