curso-plsql

CREATE OR REPLACE TRIGGER trigger4
AFTER INSERT ON USER_COMUN
FOR EACH ROW
DECLARE
var1 VARCHAR2(100);
var2 VARCHAR2(100);

BEGIN
var1 := SUBSTR(:NEW.sal_usu,0,2);
var2 := SUBSTR(:NEW.sal_usu,3,4);
dbms_output.put_line('Valor de la variable1: ' || var1);
dbms_output.put_line('Valor de la variable2: ' || var2);

INSERT INTO USER_EXP1
(COD1, NOM_USU)
VALUES
(:NEW.COD_USU, var1);
dbms_output.put_line('Insercion en la tabla user_exp1');

INSERT INTO USER_EXP2
(COD2, SAL_USU)
VALUES
(:NEW.COD_USU, var2);
dbms_output.put_line('Insercion en la tabla user_exp2');
END ;
/

http://www.adp-gmbh.ch/ora/sql/trigger/new_old.html

CREATE OR REPLACE TRIGGER trigger3
AFTER INSERT ON USER_COMUN
FOR EACH ROW

BEGIN
INSERT INTO USER_EXP1
(COD1, NOM_USU)
VALUES
(:NEW.COD_USU, :NEW.NOM_USU);

INSERT INTO USER_EXP2
(COD2, SAL_USU)
VALUES
(:NEW.COD_USU, :NEW.SAL_USU);
END ;
/

Crear tres tablas:

Tabla USER_INICIO
USER_DIR
USER_COD_POST

USER_INICIO COD,NOMBRE,COD_POST, DIR
USER_DIR COD,DIR
USER_COD_POST COD, COD_POST

CREAR UN TRIGGER QUE GENERE SEGUN SE INSERTA
CONTENDIOS EN LAS TABLAS USER_DIR, USER_COD_POST

0111, JOSE, 28045,c/VAEL 2

http://www.cs.odu.edu/~ibl/450/common/sqlstring.html

CREATE OR REPLACE TRIGGER trigger1
AFTER INSERT ON emp
FOR EACH ROW WHEN (NEW.sal > 1000)

DECLARE
sal VARCHAR2(100);

BEGIN
INSERT INTO USER_TEMP
(user_name, user_salary)
VALUES
(:NEW.ename, :NEW.sal);
END ;
/

select * from having rowid=(select max(rowid) from )

CREATE OR REPLACE PROCEDURE salariomax
IS
CURSOR salario
IS
SELECT ename,sal FROM emp;
nombre VARCHAR2(20);
sal NUMBER;
var1 NUMBER;

BEGIN
var1:=0;
OPEN salario;
LOOP

FETCH salario INTO nombre,sal;
EXIT WHEN salario%NOTFOUND;
if (sal>1000) THEN
var1:=var1+sal;
dbms_output.put_line(nombre||sal);
END IF;
--EXIT WHEN salario%NOTFOUND;
-- dbms_output.put_line(nombre||sal);
END LOOP;
CLOSE salario;
insert INTO USER_TEMP values('total',var1);

END;
/