- #POSTGRESQL ALTER TABLE DROP TRIGGER EXAMPLE UPDATE#
- #POSTGRESQL ALTER TABLE DROP TRIGGER EXAMPLE CODE#
#POSTGRESQL ALTER TABLE DROP TRIGGER EXAMPLE UPDATE#
Similar triggers can specify appropriate actions for UPDATE and DELETE. The actions then insert new rows or update existing rows, as appropriate. The actions shown for rows being inserted into the MANAGER_INFO view first test to see if appropriate rows already exist in the base tables from which MANAGER_INFO is derived. UPDATE project SET project.level = :n.level
REFERENCING NEW AS n - new manager information SELECT e.name, e.empno, d.dept_type, d.deptno, p.level,
The following example shows an INSTEAD OF trigger for inserting rows into the MANAGER_INFO view. If a view contains pseudocolumns or expressions, you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.
joins (a subset of join views are updatable). GROUP BY, CONNECT BY, or START WITH clauses. Users write normal UPDATE, INSERT, and DELETE statements against the view, and the INSTEAD OF trigger works invisibly in the background to make the right actions take place.īy default, INSTEAD OF triggers are activated for each row (see "FOR EACH ROW Option" on page 13-7).Ī view cannot be modified by UPDATE, INSERT, or DELETE statements if the view query contains any of the following constructs: The trigger performs update, insert, or delete operations directly on the underlying tables. These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement. INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through UPDATE, INSERT, and DELETE statements. The INSTEAD OF option in the CREATE TRIGGER statement is an alternative to the BEFORE and AFTER options. Alternatively, with AFTER row triggers, the data blocks need only be read once for both the triggering statement and the trigger. With BEFORE row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. This script automatically runs all of the scripts required for, or used within, the procedural extensions to the Oracle Server.ĪFTER row triggers are slightly more efficient than BEFORE row triggers. For more realistic examples of CREATE TRIGGER statements, see "Examples of Trigger Applications" on page 13-22.īefore creating any triggers, while connected as SYS, submit the CATPROC.SQL script. The following sections use this example to illustrate the way that parts of a trigger are specified. The CREATE (or CREATE OR REPLACE) statement fails if any errors exist in the PL/SQL block. The trigger will fire once for each row that is updated, and it prints the new and old salaries, and the difference. UPDATE emp SET sal = sal + 500.00 WHERE deptno = 10 The following statement creates a trigger for the EMP table:ĭbms_output.put('Old salary: ' || :old.sal) ĭbms_output.put(' New salary: ' || :new.sal) ĭbms_output.put_line(' Difference ' || sal_diff) When using an interactive tool, a solitary slash ( / ) on the last line is used to activate the CREATE TRIGGER statement. This command can be used with any interactive tool, such as SQL*Plus or Enterprise Manager. Triggers are created using the CREATE TRIGGER command. For example, creating an AFTER UPDATE statement trigger on the EMP table that itself issues an UPDATE statement on EMP causes the trigger to fire recursively until it has run out of memory. Be careful not to create recursive triggers. #POSTGRESQL ALTER TABLE DROP TRIGGER EXAMPLE CODE#
If the logic for your trigger requires much more than 60 lines of PL/SQL code, it is better to include most of the code in a stored procedure, and call the procedure from the trigger. Limit the size of triggers (60 lines or fewer is a good guideline).For example, do not define triggers to enforce data integrity rules that can be easily enforced using declarative integrity constraints. Do not define triggers that duplicate the functionality already built into Oracle.Use database triggers only for centralized, global operations that should be fired for the triggering statement, regardless of which user or database application issues the statement.Use triggers to guarantee that when a specific operation is performed, related actions are performed.Use the following guidelines when designing triggers: If you are using Trusted Oracle, see the Trusted Oracle documentation for more information about defining and using database triggers.
0 kommentar(er)
