I have created both a trigger and a procedure.
The idea behind the trigger is to call the procedure based on the criteria in the trigger.
The trigger is simple; when a particular event occurs it fires off. From the Oracle table that this event occurred, I'd like to pass a particular column to the procedure.
I have tested the procedure separately, and know that it is working, but the trigger does not seem to be working. Can anyone help me figure out what how to call the procedure from the trigger and pass a parameter to the procedure.
CREATE OR REPLACE TRIGGER trig_name
INSERT OR UPDATE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (new.cd = 'event1' AND new.cd Is Not Null)
-- I've been talking to a couple of people about it and someone had mentioned that you can't have commits in your procedure; is that true? And any suggestions on this code are greatly appreciated.
That is correct, you cannot commit within a trigger except in an autonomous transaction, which is very rarely what you want (it is only really suitable for auditing failed DML). In fact, it is bad practice to commit within stored procedures for this kind of reason: it limits the usability of the procedure. Commiting (or rolling back) should be done by the caller of the procedure, which may want to do other things besides call that procedure before committing (like you do here).