Answer Posted / aadi

Using an INSTEAD OF Trigger to Update on a View

After you create one or more tables (like those named dept
and emp in the following example), and then created a view
(like the one named manager_info) from dept and emp, you can
use an INSTEAD OF trigger to update that view.

The following CREATE TRIGGER statement creates
manager_info_update, an INSTEAD OF trigger that is designed
to update rows within the dept and emp tables through the
manager_info view.

CREATE TRIGGER manager_info_update
INSTEAD OF UPDATE ON manager_info
REFERENCING NEW AS n
FOR EACH ROW
(EXECUTE PROCEDURE updtab (n.empno, n.empname,
n.deptno,));

CREATE PROCEDURE updtab (eno INT, ename CHAR(20), dno INT,)
DEFINE deptcode INT;
UPDATE dept SET manager_num = eno where deptno = dno;
SELECT deptno INTO deptcode FROM emp WHERE empno = eno;
IF dno !=deptcode THEN
UPDATE emp SET deptno = dno WHERE empno = eno;
END IF;
END PROCEDURE;

After the tables, view, trigger, and SPL routine have been
created, the database server treats the following UPDATE
statement as a triggering event:

UPDATE manager_info
SET empno = 3666, empname = "Steve"
WHERE deptno = 01;

This triggering UPDATE statement is not executed, but this
event causes the trigger action to be executed instead,
invoking the updtab() SPL routine. The UPDATE statements in
the SPL routine update values into both the emp and dept
base tables of the manager_info view.

What is sql key?

931

---

How do I copy a table in sql?

938

---

How much ram can sql express use?

940

---

What is the difference between rollback and rollback to statements?

1024

---

How to get each name only once from an employee table?

1071

---

What is the file extension for sql database?

951

---

give the syntax of grant and revoke commands? : Sql dba

1094

---

What is basic structure of pl sql?

1003

---

What is the most important ddl statements in sql are?

953

---

How do you get column names only for a table (sql server)?

1109

---

What is difference between stored function and application function?

1117

---

What are all types of user defined functions?

1015

---

how can I make a script that can be bi-language (supports english, german)? : Sql dba

950

---

What information is needed to connect sql*plus an oracle server?

986

---

What is use of trigger?

881

---