| URGENT help on a TRIGGER PLEASE!!!! [message #1827] |
Mon, 03 June 2002 02:49  |
Australia
Messages: 4
Registered: May 2002
|
Junior Member |
|
|
Hi, can someone please help e with this trigger. I have done it, but i dont think its right. here is the pseudo code, followed by what code i have written for the trigger. Help will be GREATLY appreciated!
PSEUDO CODE:
trigger name: aft_in_del_people_row_tgr
event: insert or delete on people
timing nad type: before row
action:
if the user issuing the update does not have username 'ADMIN'then
raise error (-20030 'User not permitted to perform action')
end if
if there is no change_log row or the relevant people id then
create such a change_log row with all counts zero and today's date
end if
increment the appropriate count, icount or dcount, of the relevant change_log row
MY CODE:
create or replace trigger aft_in_del_people_row_tgr
before insert or delete on people
for each row
begin
if(:new.username<> lower(user)) and user <> 'ADMIN' then
raise_application_error (-20030, 'User not permitted to perform action');
end if;
if sql%rowcount = 0 then
insert into change_log values(:new.people_id, 1, sysdate);
end if;
update change_log
set ucount = ucount + 1
where people_id = :new.people_id;
end;
/
|
|
|
|
| Re: URGENT help on a TRIGGER PLEASE!!!! [message #1837 is a reply to message #1827] |
Mon, 03 June 2002 22:11  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
I don't get it. On the one hand you're talking about a trigger before insert/delete and on the other hand you're talking about an 'user issuing the update'. I assume the following:
1.You want to log/audit all inserts/deletes on the people table (whether successful or not).
2.Updates are irrelevant (?)
3.Deletes refer in the change_log table to people_ids that are non-existant. (?)
A curious way of writing, but since I don't know your model, it could be possible. Anyway, here's a quick&dirty solution. Don't get this as the ultimate solution, it is a pointer in the direction:
CREATE OR REPLACE TRIGGER bidr_people_row_trg
BEFORE INSERT OR DELETE
ON people
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
CURSOR c_change_log
IS
SELECT rowid
FROM change_log
WHERE people_id = NVL(:new.people_id,:old.people_id);
v_log_rowid ROWID;
BEGIN
OPEN c_change_log;
FETCH c_change_log INTO v_log_rowid;
IF c_change_log%NOTFOUND THEN
INSERT INTO change_log
VALUES(NVL(:new.people_id,:old.people_id), 1, sysdate);
ELSE
UPDATE change_log
set ucount = ucount+1
where rowid = v_log_rowid;
END IF;
IF (lower(:new.username) != lower(user)) and user != 'ADMIN' THEN
RAISE_APPLICATION_ERROR(-20030,'User not permitted to update people table!');
END IF;
END;
/
A reminder: deletes don't have :new values, only :old values.
HTH,
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
