Triggers

PRACTICA TRIGGERS

Paso 1

Paso 2 DROP TABLE IF EXISTS t1; CREATE TABLE IF NOT EXISTS t1 ( employeeIdint(11) NOT NULL default '0', default NULL,

titleOfCourtesyvarchar(25)subAlternovarchar(32) Jefe ) varchar(32)

NOT NULL default '',

default NULL

Paso 3 INSERT INTO t1 SELECT a.employeeId, a.titleOfCourtesy, concat(a.lastName, ', ', a.firstName ) AS subAlterno,concat(j.lastName, ', ', j.firstName ) AS Jefe FROM employees a LEFT JOIN employees j ON a.reportsto = j.employeeid

Paso 4 SELECT * FROM t1 ORDER BY 1 El resultado sería el mismo si ejecutamos el siguiente código:SELECT a.employeeId, a.titleOfCourtesy, concat(a.lastName, ', ', a.firstName ) AS subAlterno, concat(j.lastName, ', ', j.firstName ) AS Jefe FROM employees a LEFT JOIN employees j ON a.reportsto =j.employeeid

Paso 5 CREATE TABLE t2 as SELECT a.employeeId, a.titleOfCourtesy, concat(a.lastName, ', ', a.firstName ) AS subAlterno, concat(j.lastName, ', ', j.firstName ) AS Jefe FROM employees aLEFT JOIN employees j ON a.reportsto = j.employeeid

Paso 7 CREATE TABLE t3 as selecto.orderid, p.ProductName, (od.unitprice * od.quantity) as Venta, concat(od.discount * 100,'%') as Descto,(od.unitprice * od.quantity) * (1 - od.discount) as Neto fromorder_details od, orders o, products p whereod.orderid = o.orderid andod.productId = p.productId

Paso 8 CREATE TRIGGER t1_insert AFTER INSERTON t1 FOR EACH ROW INSERT INTO t2 values ( NEW.employeeId, NEW.titleOfCourtesy, NEW.subAlterno, NEW.Jefe);

Paso 9 INSERT INTO t1 (employeeId, titleOfCourtesy, subAlterno, Jefe) VALUES (10, 'Mr.','Reyes, Luis', 'Fuller, Andrew'); INSERT INTO t1 (employeeId, titleOfCourtesy, subAlterno, Jefe) VALUES (5, 'Sr.', 'Menjivar, jose', 'Beginer, Mateo'); INSERT INTO t1 (employeeId, titleOfCourtesy,subAlterno, Jefe) VALUES (3, 'Sra.', 'Cuellar, Antonia', 'Fuller, Maria'); INSERT INTO t1 (employeeId, titleOfCourtesy, subAlterno, Jefe) VALUES (7, 'Mr.', 'Peres, Raul', 'Fuller, Stanley'); INSERT...