SQL & PL/SQL :: Trigger To Insert Record Into Another Table
Aug 17, 2010
writing a trigger body. My requirement is i need to insert a new record in a task table when ever a new record is inserted into employee table.Here in the trigger i need to select the name of the employee in the last inserted row in employee table and insert the name in task table.I tried to write the code as below
insert into task(name, date, type) values ((select name from employee where emp_id=(select max(emp_id) from employee), sysdate, 'document'));
When i am trying to insert record using trigger, it is taking last but one record from the employee table.
i have one table ot_ins_item where user will enter the details of item, grade,item qty , later on user will go and update the same table the details of different grades received for the same item in different columns with qty breakup in 3 different fields it_qty_01 , it_qty_02,it_qty_03 respectively with different grades , what i need is i want is whenever he updates this table with different grades based on data entered in 3 different fields , a procedure or trigger should delete the initial record saved and insert three different rows based on newly updated values , it may be 3 or it may be 2 sometime depending upon input values that many records should be inserted same time controlling the qty's entered in breakup not exceeding the main qty.
INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'A','ITEM1',NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.NEXTVAL,'B','1TEM2',NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO OT_INS_ITEM VALUES (1,s_it_no.nextval,'C','ITEM3',NULL,NULL,NULL,NULL,NULL,NULL); SELECT * FROM OT_INS_ITEM; [code]....
I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.
I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".
Oracle 11gI have a large table of 125 million records - t3_universe. This table never gets updated or altered once loaded, but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads - that will be updated with regard to when the lead is mailed and for other relevant information. select records from this 125 million record table to insert into the smaller table.
I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below. Then use a second query that inserts into T3_Leads from this View V_Market. This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause? My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.
CREATE VIEW V_Market asWITH got_pairs AS ( SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */ l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no , ROW_NUMBER () OVER ( PARTITION BY l.address_key ORDER BY l.hh_verification_date DESC ) AS r_num FROM t3_universe e JOIN t3_universe l ON l.address_key = e.address_key AND l.zip_code = e.zip_code AND l.p1_gender != e.p1_gender
I have looked at the code you pointed me to, and have attempted to get it to work using a package, but I cant even get the package to compile..
CREATE OR REPLACE PACKAGE BODY trigger_api AS PROCEDURE tab1_row_change (p_numass IN varchar2, p_datcre IN date) IS BEGIN INSERT INTO tempjob (numass, datecre) VALUES (p_numass, p_datcre); END tab1_row_change; [code]....
Doing this process from code is not an option and MUST happen automatically via triggers.The mutating trigger error can sometimes be avoided: URL....
I am trying to create a trigger which does the following : A flag in the initial able is set to Y. When this happens, the record needs to be inserted into a history table and then DELETED from the calling table.
It must happen in triggers, but I keep getting the mutating error.I have tried to use a Compound trigger, but with no luck and just dont really understand how to get this to work.
Doing this process from code is not an option and MUST happen automatically via triggers.
I am trying to create a trigger which does the following : A flag in the initial able is set to Y. When this happens, the record needs to be inserted into a history table and then DELETED from the calling table. It must happen in triggers, but I keep getting the mutating error. I have tried to use a Compound trigger, but with no luck and just dont really understand how to get this to work.
Doing this process from code is not an option and MUST happen automatically via triggers.
In the trigger I am calling a procedure that returns an error if there is any error returned from procedure. I have to update the table table1's column error_desc (for the same new inserted record for which the trigger was called) with the error received by OUT parameter of procedure called in trigger. I have to update the same record on whose insert this trigger was called.
I'm trying to create a trigger so that whenever a record in the Employee table is deleted, a trigger will automatically delete corresponding records in the Job History table, then the Employee record is archived to EmployeeArchive before it is deleted. It compiles but with warnings. Here's what I've got.
I have three tables. One for projects, one for volunteers, and a bridge entity for the many to many relationship between the Project and Volunteer.
In Project table, I have a field called, Volunteers_currently_signed_up, which means the number of volunteers currently signed up to participate in a project.
When I add an entry to my bridge entity which is composed of Volunteer_ID and Project_ID, I want the Volunteers_currently_signed_up to increment by 1, where the Project_ID in the bridge entity corresponds to that in Project.
I have very very little PL/SQL, and this is my amateur attempt so far:
CREATE OR REPLACE trigger "BI_Volunteers_currently_signed_up" BEFORE INSERT OR UPDATE ON Volunteers_in_project for each row WHERE Volunteers_in_project.Project_ID=Project.Project_ID; begin Project.Volunteers_currently_signed_up += 1; end; /
We would like to create functions to insert and update our tables and would like to make it not possible to update and insert the table directly outside of the function. Is there a way to do that in the trigger?
I have table t1 and t1 , I want a procedure that will insert all records from t1 into table t2 and after successfull insert table t1 should be truncated .
If their is any problem in insert in to table t2 , the truncate command should not work .
Truncate command should work only after successfully insert command .
In a trigger(on update of a table t1) I am trying to write, I am doing an insert on t2 accessing ':new' values of the update on t1.
But in my Insert statement, I am having get one of the column values from another table. How can I write my insert statement in such a way as to insert values contained in ':new' pseudo columns and a select from another table. Below is my insert statement in the trigger : -------
IF (:old.GROUP_YELLOW <> :new.GROUP_YELLOW) THEN INSERT INTO TEST.W_THRESHOLD_LOG (THRESHOLD_LOG_WID, CHANGE_DATE, MEASURE_TYPE_WID, MEASURE_NAME, CUSTOMER_WID, CUSTOMER_NAME, USER_ID, CHANGED_ITEM, PREV_VALUE, NEW_VALUE) VALUES(TEST.W_THRESHOLD_LOG_SEQ.NEXTVAL, SYSDATE, :new.MEASURE_TYPE_WID, 'Rolling Stabilty' , :new.CUSTOMER_WID, 'Customer1', 'User1', 'GROUP_YELLOW', :old.GROUP_YELLOW , :new.GROUP_YELLOW); END IF; -------
In the above code if the hardcoded value 'Customer1' need to be picked from another table, i.e .
SELECT NAME FROM W_CUSTOMER_DIM WHERE CUSTOMER_WID = THRESHOLD.CUSTOMER_WID
how can I rewrite my query to the above value from the select into my insert statement..?
I would like to UPDATE the columns p1 and p2 of my table student (studentid:pk,name,p1,p2,...) for a given studentid.and I have a when-button-pressed trigger with this
UPDATE student SET student.p1=:validation.proj1, student.p2=:validation.proj2 where UPPER(student.studentid)=UPPER(:validation.studentid);commit_form;
when I run my form with a correct studentid, I got this error: FRM-40508: ORACLE error: UNABLE to INSERT record
but it is cworking correctly in sqlplus; and I have all priveligies.
I know this is an old thread and I just started working with triggers. I want to check if the data being inserted is already in the table and if not insert it:
create or replace trigger t_triggername before insert on tbl_tablename for each row begin if(:new.user_id <> :old.user_id) then insert into tbl_tablename(user_id, location) values (:new.user_id, :new.location); end if; end;
what if I wanted to keep the user but only update location if the user is already in the table. I've tried doing it this way:
create or replace trigger t_triggername before insert on tbl_tablename for each row begin if(:new.user_id <> :old.user_id) then insert into tbl_tablename(user_id, location)
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end BEFORE INSERT OR UPDATE OF issued ON shares_amount FOR EACH ROW DECLARE BEGIN INSERT INTO shares_amount(date_end) VALUES(SYSDATE); END set_date_end; /
I'm trying to insert only a few columns (not all of them) from temp_ioi_010209 into mtl_system_items_interface. Both of these tables have more columns than just the 7 I'm specifying but everything I found under INSERT ALL here makes me think I'm doing it right. According to the DESCRIBE of mtl_system_items_interface the only non-nullable column is set_process_id and I'm specifying that one...
Why I'm getting the error "ORA-00947: not enough values"?
INSERT ALL INTO mtl_system_items_interface VALUES (process_flag, transaction_type, set_process_id, [code].......
the following case is successfully done with mssql databases.
Case:
Table UserGroup Columns id, name, handshake
When the handshake is set to 'd', this record should be deleted. I know it is bad behaviour by design.
What have I done so far:
- created an after update trigger (mutual error) Caused by trying a delete action in the update action, not possible.
- created a view in combination of instead of update trigger.
This causes also mutual error, or if ignored (PRAGMA AUTONOMOUS_TRANSACTION), an deadlock.
Code so far:
create or replace procedure Delete_UserGroup_sp(p_groupId in USER_GROUP.HMIUSERGROUPID%TYPE, p_handshake in USER_GROUP.HANDSHAKE%TYPE) is begin if p_handshake = 'd' then delete USER_GROUP WHERE HMIUSERGROUPID = p_groupId; commit; end if; end;
create or replace view USERGROUP_V as select * from USER_GROUP
create or replace trigger USER_GROUP_T1 instead of update on USERGROUP_V for each row declare PRAGMA AUTONOMOUS_TRANSACTION; begin Delete_UserGroup_sp(:new.HMIUSERGROUPID, :new.HANDSHAKE); end;
I want to execute a db trigger once for updation of more than one record on a single primary key. The updation would be made by a 10g form by using the common tabular data block.
I am trying to write a procedure using when-validate-record that will clear all fields, display a message and ignore all commits if user is not authorized to have access to make changes to the form.
I have tried to use Clear_form(no_validate)and clear_record and neither works
I have a trigger which monitors the deletion activity on the table. But I would like to re-insert those records to the in the table using the same trigger.
