| triggers help URGENT!! [message #38947] |
Thu, 30 May 2002 06:05  |
SiXy
Messages: 3
Registered: May 2002
|
Junior Member |
|
|
Hey can someone please help me with this trigger, im not sure how to do it, or if i have started it off right.
/*Trigger b, psuedo code
trigger name: bfr_up_people_row_tgr
event: update on people
timing and type: before row
action:
if the user issuing the update does not have username "ADMIN' or the (lower-case) username
identified in the existing (old) username field of the row being changed
raise error -20030 ('User not permitted to perform action')
end if
if the update will change the username coloumn,
reise error -20040 ('Column username cannot be updated')
end if
if there is no change_log row for the current (new) people id, then
create such a change_log row with all counts zero
end if
increment the ucount of the relevant change_log row
HINT: Instead of ADMIN use your username when testing but do not forget to turn it back.
*/
create or replace trigger bfr_up_people_row_tgr
before update on people
for each row
declare
username string;
begin
if(username != 'ADMIN' or 'admin') then
raise_application_error(-20030, 'User not permitted to perform action');
end if;
/*and i dont know how to do the rest*/
end;
/
|
|
|
|
| Re: triggers help URGENT!! [message #38950 is a reply to message #38947] |
Thu, 30 May 2002 08:25  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
When you refer to username, is this the Oracle user name? I just wanted you to clarify this because sometimes, by username, people mean the id used to login to the app, which is not always the same as the username of the Oracle connection.
|
|
|
|
|
|
|
|
| Re: triggers help URGENT!! [message #38969 is a reply to message #38947] |
Fri, 31 May 2002 08:40 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Well, if the username is the username used to login to the app and _not_ the database, you would need to set a package variable to the value of that username so the trigger can see it. I will give you an example here though that is based on the Oracle user (using the built-in USER value) and maybe you can take it from there:
create or replace trigger bfr_up_people_row_tgr
before update on people
for each row
begin
if (:old.username <> :new.username) then
raise_application_error(-20040, 'Column username cannot be updated');
elsif (:new.username <> lower(user)) and user <> 'ADMIN' then
raise_application_error(-20030, 'User not permitted to perform action');
end if;
update change_log
set ucount = ucount + 1
where people_id = :new.people_id;
if sql%rowcount = 0 then
insert into change_log values (:new.people_id, 1);
end if;
end;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
