Triggers

Trigger

It is a PL/SQL procedure that executes when a table is modified. Triggers can be fired before or after that event that modifies the table occurs.
Es un procedimiento PL/SQL que se ejecuta cuando una tabla se modifica. Los Triggers se disparan antes o después que el evento que modifica la tabla ocurra.

Tip
A Trigger can be fired due to the execution of an INSERT, a DELETE, or an UPDATE. The Trigger is used in combination with the BEFORE or AFTER commands to indicate when the event is fired, before or after.
El Trigger puede dispararse debido a la ejecución de un INSERT, un DELETE o un UPDATE. El Trigger se usa en combinación con los comando BEFORE o AFTER para indicar cuándo se dispara el evento, antes o después.

Tip
Triggers can be used to:
  1. Enforce complex business rules,
  2. Compute a value that is based on other values or circumstances,
  3. To audit an action, and to implement security measures.
Los Triggers se usan para
  1. Forzar a que se cumplan reglas de negocios complejas,
  2. Calcular un valor usando otros valores o bajo ciertas circunstancias,
  3. Propósitos de monitoreo y de auditoría.

Tip
By default, only the user who created the table can set a trigger on the table.
Por defecto, solamente el usuario quien creó la tabla puede fijar un trigger en la tabla.

Problem 1
circuit_city > Modify the circuit_city.sql script to create the audit_ord_det table to track the DELETE, UPDATE and INSERT operations on the ord_det table. The time_date column stores when the operation was executed in the ord_det table. Using: (a) Oracle, (b) Microsoft SQL Server.
Modifique el script circuit_city.sql para crear la tabla audit_ord_det que llevará un seguimiento de DELETE, UPDATE e INSERT en la tabla ord_det. La columna time_date indica cuando se ejecutó el comando en la tabla ord_det. Usando: (a) Oracle, (b) Microsoft SQL Server.

circuit_city.sql (Oracle)
CREATE TABLE audit_ord_det
(
      audit_ord_det_id INT NOT NULL PRIMARY KEY,
     orden_id INT NOT NULL REFERENCES orderx(orden_id),
     action VARCHAR(10) NOT NULL CHECK( action = 'DELETE' OR action = 'UPDATE' OR action =      'INSERT'),
     time_date DATE NOT NULL
);
CREATE SEQUENCE seq_audit_ord_det START WITH 1 MAXVALUE 9999;


Tip
The DROP TRIGGER command can be used to delete a Trigger. However, Triggers are deleted when droping the table that is associated with the trigger.
El comando DROP TRIGGER puede usarse para borrar un Trigger. Sin embargo, los Triggers son eliminados al borrar la tabla asociada con el trigger.

Tip
When a trigger is executed in Oracle, it is possible to access the row that fired the trigger. The original row is called :OLD, while the new row is called :NEW. To access individual values in the row the dot operator is used. Observe that :OLD and :NEW are records.
Durante la ejecución de un Trigger en Oracle es posible accessar el renglón de la tabla que disparó el trigger. El renglón original se llama :OLD, mientras que el renglón nuevo se llame :NEW. Para accessar los valores individuales en el renglón se usa el operador de punto. Observe que :OLD y :NEW son registros.

Tip
Because Microsoft SQL Server does not support the FOR EACH ROW command, a Trigger can get access to the rows the fired the Trigger using the tables: INSERTED, UPDATED or DELETED. A CURSOR is required to access each individual row.
Ya que Microsoft SQL Server no soporta el comando FOR EACH ROW, un Trigger puede accesar los renglones que encendieron el Trigger por medio de las tablas INSERTED, UPDATED o DELETED para accesar los renglones que encendieron el Trigger. Para procesar cada renglón se debe usar el comando CURSOR.

Problem 2
circuit_city > Modify the circuit_city.sql script to create the three triggers shown. The first Trigger is fired after an INSERT in the ord_det table is performed. The second Trigger is fired after an UPDATE in the ord_det table is performed. Using: (a) Oracle, (b) Microsoft SQL Server.
Modifique el script circuit_city.sql para crear los tres Triggers mostrados. El primer Trigger se enciende después de realizar un INSERT a la tabla ord_det. El segundo Trigger se enciende después de realizar un UPDATE a la tabla ord_det. Usando: (a) Oracle, (b) Microsoft SQL Server.

circuit_city.sql (Oracle)
CREATE OR REPLACE TRIGGER trig_ins_ord_det
AFTER INSERT
ON ord_det
FOR EACH ROW
BEGIN
INSERT INTO audit_ord_det VALUES(seq_audit_ord_det.nextval, :new.orden_id, 'INSERT', CURRENT_DATE);
END trig_ins_ord_de;
/

CREATE OR REPLACE TRIGGER trig_upd_ord_det
AFTER UPDATE
ON ord_det
FOR EACH ROW
BEGIN
INSERT INTO audit_ord_det VALUES(seq_audit_ord_det.nextval, :old.orden_id, 'UPDATE', CURRENT_DATE);
END trig_upd_ord_det;
/

CREATE OR REPLACE TRIGGER trig_del_ord_det
AFTER DELETE
ON ord_det
FOR EACH ROW
BEGIN
INSERT INTO audit_ord_det VALUES(seq_audit_ord_det.nextval, :old.orden_id, 'DELETE', CURRENT_DATE);
END trig_del_ord_det;
/


circuit_city.sql (Microsoft SQL Server)
CREATE TRIGGER trig_ins_ord_det
ON ord_det
AFTER INSERT
AS
BEGIN
DECLARE @p_order_id INT;
DECLARE cur_order CURSOR READ_ONLY FOR SELECT order_id FROM INSERTED
OPEN cur_order
FETCH NEXT FROM cur_order INTO @p_order_id
WHILE (@@FETCH_STATUS=0)
BEGIN
INSERT INTO audit_ord_det (order_id, action) VALUES(@p_order_id, 'INSERT');
FETCH NEXT FROM cur_order INTO @p_order_id;
END
CLOSE cur_order;
DEALLOCATE cur_order;
END
GO
...


© Copyright 2000-2021 Wintempla selo. All Rights Reserved. Jul 22 2021. Home