I haven't tried this, ie I don't even know if what you're trying to do is possible, but I'm guessing that your block was meant to be...
I'm also guessing your db version:-)
Also, does it not work because of an error, or does the code execute okay but not do what you expect? Maybe you need to be a bit more specific than just saying "it does not work".
From the comments in the dbms_alert package spec....
-- Remove all alerts for this session from registration list. Do this
-- when the session is no longer interested in any alerts. Removing
-- alerts is important since it will reduce the amount of work done
-- by signalers of the alert. If a session dies without removing all
-- of its alerts, the alerts will eventually (but not immediately)
-- be cleaned up. This call always performs a commit.
-- This procedure is called automatically upon first reference to this
-- package during a session. Therefore no alerts from prior sessions
-- which may have terminated abnormally can affect this session.
Thank you for your assistance. I should have included the dbms_alert package to invoke the procedure. That was stupid of me. Anyway, of course that worked just fine.
I have another error, though.
create or replace trigger security
before insert or update or delete on employees
for each row
dbms_alert.signal('emp_change','NOTICE: OLD id: ' || :OLD.emp_id
|| ' NEW id: ' || :NEW.emp_id || ' OLD Pay Rate: '
|| :OLD.pay_rate || ' NEW pay rate: ' || :NEW.pay_rate);
insert into security