Oracle trigger inserting updating deleting
If same had to be done without compound-trigger, it might have been required to share data using packages. A beauty of this approach is we were able to access same data ‘v_emp_changes’ between statement and row triggering events.The compound trigger is useful when you want to accumulate facts that characterize the “for each row” changes and then act on them as a body at “after statement” time. With this in mind, one can see that it make sense to move variable declared in trigger body can be used and assigned value of logged in user in the declarative section itself.Nr,(SELECT COUNT(*) FROM cs Forum Posts nfp WHERE nfp. Nr)as cnt,(SELECT MAX(Nr) FROM cs Forum Posts nfp2 WHERE nfp2. It would be better to describe the aim you want to achieve in words.' I Thought when somebody search for a solution and need an advice about some code in oracle he need to show the code directly..
The Trigger is: ============== CREATE TRIGGER TRG_Forum Posts ON Forum Posts FOR INSERT, UPDATE, DELETE AS SET NOCOUNT ON IF UPDATE(nr Topic) OR (EXISTS(select 1 from deleted) AND NOT EXISTS(select 1 from inserted)) BEGIN ...........
But before that we need to create our Tables: Now the trigger… Hope that helped to have a better understanding of Compound Triggers in Oracle.
On update of each row instead of performing an insert operation for each field, we store (buffer) the required attributes in a Arrays of type aud_emp.
In Oracle, these values are stored in OLD and NEW records.
So I would write something like CREATE TRIGGER TRG_Forum Posts BEFORE INSERT OR UPDATE OF nr Topic OR DELETE ON Forum Posts FOR EACH ROW DECLARE nr_topic_old integer; nr_topic_new integer; cnt integer; nr_lastpost integer; TYPE REF_cur_topics IS REF CURSOR; cur_topics REF_cur_topics; BEGIN -- do not know what is in :new in delete and in :old in insert -- i am afraid it contains nulls which is not wise to use -- you can check it yourself as i do not have access to Oracle now IF DELETING THEN nr_topic_old := :Topic; nr_topic_new := :Topic; ELSIF INSERTING THEN nr_topic_old := :Topic; nr_topic_new := :Topic; ELSE nr_topic_old := :Topic; nr_topic_new := :Topic; END IF; -- your commands, use nr_topic_old and nr_topic_new variables OPEN cur_topics FOR SELECT c. Nr in ( nr_topic_new, nr_topic_old ); END; / 'I do not think it is good to publish here sqlserver code as it seems nobody here knows its meaning.