Hello John,
try this
CREATE OR REPLACE TRIGGER DBUSER.TRIG_TEST2 BEFORE DELETE ON DBUSER.TEST2
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
DECLARE
S_DUMMY VARCHAR2(2);
BEGIN
IF USER <> :old.userid THEN
BEGIN
SELECT 'OK' INTO S_DUMMY FROM SESSION_ROLES
WHERE ROLE = 'TESTROLE';
EXCEPTION
WHEN NO_DATA_FOUND THEN
exc_handler.raise_error(exc_handler.en_del_not_your_rec);
END;
END IF;
END;
H. John C. Hopkins <john_nospam_at_hpe.ufl.edu> wrote in article
<6a5gf7$s2n_at_no-names.nerdc.ufl.edu>...
> Hello.
>
> I'm trying to write a Before Delete trigger that will check whether or
not
> the current user created the record s/he's trying to delete. (The
creator's
> user name is stored in the field userid.) This restriction should only
> apply to users who are assigned the role TESTROLE.
>
> Here's the logic (code follows):
>
> The cursor checks the current user's privileges to see if they've been
> assigned the TESTROLE. Their user name is put into temp_user to test if
it
> matches the userid that created the current record. If the current user
> name does not match the userid stored in the record, then the cursor is
> opened, and a record is fetched.
>
> If a record is fetched successfully (%FOUND = TRUE), it should mean that
> this user is assigned the TESTROLE role, meaning they can't delete a
record
> they didn't create. An error handling routine (in package exc_handler)
is
> called to interrupt the delete and notify the user.
>
> The trigger does recognize when the current user and the userid do not
> match. However, it fails to raise the error after opening the cursor and
> finding a record. I must be misinterpreting the %FOUND attribute of the
> cursor. What am I missing, and how might I improve the code?
>
> Thanks
>
> -John
>
> CREATE OR REPLACE TRIGGER DBUSER.TRIG_TEST2 BEFORE DELETE ON DBUSER.TEST2
> REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
> DECLARE
> CURSOR cur_clerk IS
> SELECT username
> FROM user_role_privs
> WHERE granted_role = 'TESTROLE';
> rec_clerk cur_clerk%ROWTYPE;
> temp_user VARCHAR2(8);
> BEGIN
> SELECT user INTO temp_user FROM dual;
> IF temp_user != :old.userid
> THEN
> OPEN cur_clerk;
> FETCH cur_clerk into rec_clerk;
> IF cur_clerk%ROWCOUNT != 0
> THEN
> exc_handler.raise_error(exc_handler.en_del_not_your_rec);
> END IF;
> CLOSE cur_clerk;
> END IF;
> END;
>
>
>
>
Received on Thu Jan 22 1998 - 00:00:00 CST
