Pl sql inserting updating
Whenever user will UPDATE data of bank_transactions, the old data will be inserted into bank_transactions_audit by Trigger for audit or backup purpose.First, we will create transaction table bank_transactions.Suppose some company have job openings and already having application data and the criteria is (i) Job Experience must be more than or equal to 3 years and (ii) Previous application attempt must not be done in last 2 years.To ensure data intigrity, We will create before UPDATE trigger and it will restrict UPDATE on data which violates any of above criteria. Then we will create “trg_before_emp_update” Trigger on columns JOB_EXPERIENCE and LAST_APPLIED_DATE of job_openings table.*Action: Correct the problem as described in the error message or contact the application administrator or DBA for more information. Whether user enters null or any values in these fields, Trigger will update those values and then in Database, newly updated values will be updated. Then we will create BEFORE UPDATE Trigger “trg_before_person_update” on person_records table.Then insert two rows in this table and observe the inserted values in DB.A suggestion: rather than check for existence insert/update, it’s usually more efficient to do it one of these ways: 1.
Every week, table STUDENT needs to be synchronized with the data in STUDENT_N – any new entries for students who attempted the GMAT to be inserted, plus corrections if any made to the existing details.
Then we will create “trg_before_update_txn_audit” Trigger on table bank_transactions.
We will insert different values in bank_transactions table.
Update first_name column of both rows with some new value and then will observe the table data. UPDATED_DATE := sysdate; END; / -- setting date format to to 'DD-MON-YYYY' alter session set nls_date_format = 'DD-MON-YYYY'; INSERT INTO person_records VALUES (101,'Devil','Mark',sysdate,null,null); INSERT INTO person_records VALUES (102,'Finch','Andrew',sysdate,null,null); (3)Logging/Auditing data before UPDATE of the data.
The value of UPDATED_BY and UPDATE_DATE will be updated by trigger. CREATE TABLE person_records ( PERSON_ID number(10) primary key, FIRST_NAME varchar2(50), LAST_NAME varchar2(50), HIRE_DATE date, UPDATED_BY varchar2(20), UPDATED_DATE date ); CREATE OR REPLACE TRIGGER trg_before_person_update BEFORE UPDATE on person_records FOR EACH ROW DECLARE username varchar2(20); BEGIN SELECT USER INTO username FROM dual; -- Setting updated_by and updated_Date values. In this example we have created a trigger which will insert rows into audit table before each updation on transaction table.