Pl sql inserting updating

For example, do not assign a value to a global package variable in a row trigger if the current value of the global variable is dependent on the row being processed by the row trigger.

Also, if global package variables are updated within a trigger, then it is best to initialize those variables in a , because a cursor must be opened for every execution of a trigger.

A database trigger is a stored procedure that automatically executes whenever an event occurs. Oracle initiates an ‘AFTER INSERT’ trigger after an insert event has been occurred and an ‘AFTER UPDATE’ trigger after an update event has been occurred.

CREATE or REPLACE TRIGGER emp_after_insert AFTER INSERT ON emp FOR EACH ROW DECLARE BEGIN insert into emp_backup values (:new.empid, :new.fname, :new.lname); DBMS_OUTPUT.

A relational database does not guarantee the order of rows processed by a SQL statement.

Therefore, do not create triggers that depend on the order in which rows are processed.

Each subsequent trigger sees the changes made by the previously fired triggers. The old values are the original values, and the new values are the current values, as set by the most recently fired trigger.Some views are inherently updatable, but others are not because they were created with one or more of the constructs listed in"Views that Require INSTEAD OF Triggers". These statements are run if the triggering statement is entered and if the trigger restriction (if included) evaluates to CONNECT system/manager GRANT ADMINISTER DATABASE TRIGGER TO scott; CONNECT scott/tiger CREATE TABLE audit_table ( seq number, user_at VARCHAR2(10), time_now DATE, term VARCHAR2(10), job VARCHAR2(10), proc VARCHAR2(10), enum NUMBER); CREATE OR REPLACE PROCEDURE foo (c VARCHAR2) AS BEGIN INSERT INTO Audit_table (user_at) VALUES(c); END; CREATE OR REPLACE TRIGGER logontrig AFTER LOGON ON DATABASE -- Just call an existing procedure.Any view that contains one of those constructs can be made updatable by using an CREATE TABLE Project_tab ( Prj_level NUMBER, Projno NUMBER, Resp_dept NUMBER); CREATE TABLE Emp_tab ( Empno NUMBER NOT NULL, Ename VARCHAR2(10), Job VARCHAR2(9), Mgr NUMBER(4), Hiredate DATE, Sal NUMBER(7,2), Comm NUMBER(7,2), Deptno NUMBER(2) NOT NULL); CREATE TABLE Dept_tab ( Deptno NUMBER(2) NOT NULL, Dname VARCHAR2(14), Loc VARCHAR2(13), Mgr_no NUMBER, Dept_type NUMBER); CREATE OR REPLACE VIEW manager_info AS SELECT e.ename, e.empno, d.dept_type, d.deptno, p.prj_level, p.projno FROM Emp_tab e, Dept_tab d, Project_tab p WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept; CREATE OR REPLACE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW AS n -- new manager information FOR EACH ROW DECLARE rowcnt number; BEGIN SELECT COUNT(*) INTO rowcnt FROM Emp_tab WHERE empno = :n.empno; IF rowcnt = 0 THEN INSERT INTO Emp_tab (empno,ename) VALUES (:n.empno, :n.ename); ELSE UPDATE Emp_tab SET Emp_tab.ename = :n.ename WHERE Emp_tab.empno = :n.empno; END IF; SELECT COUNT(*) INTO rowcnt FROM Dept_tab WHERE deptno = :n.deptno; IF rowcnt = 0 THEN INSERT INTO Dept_tab (deptno, dept_type) VALUES(:n.deptno, :n.dept_type); ELSE UPDATE Dept_tab SET Dept_tab.dept_type = :n.dept_type WHERE Dept_tab.deptno = :n.deptno; END IF; SELECT COUNT(*) INTO rowcnt FROM Project_tab WHERE Project_tab.projno = :n.projno; IF rowcnt = 0 THEN INSERT INTO Project_tab (projno, prj_level) VALUES(:n.projno, :n.prj_level); ELSE UPDATE Project_tab SET Project_tab.prj_level = :n.prj_level WHERE Project_tab.projno = :n.projno; END IF; END; triggers can also be created over nested table view columns. The ORA_LOGIN_USER is a function -- that returns information about the event that fired the trigger.If a triggering statement omits a column list, the trigger is fired when any column of the associated table is updated.A column list cannot be specified for statement trigger is fired again.

Leave a Reply