CREATE DATABASE EXAMEN
USE EXAMEN

CREATE TABLE PASAJERO(
    IDPASAJERO  CHAR(5)         NOT NULL,
    NOMBRES     VARCHAR(50)     NOT NULL,
    IDPAIS      CHAR(4)         NOT NULL,
    TELEFONO    CHAR(15)        NOT NULL,
    EMAIL       VARCHAR(50)     NOT NULL
)

CREATE TABLE pasajero_h
(
    ID_MOV INT,
       TIPO_MOV VARCHAR(20),
       --(Valores válidos: INSERT, UPDATE, DELETE)
        ID_CAMPO VARCHAR (20),
       VALOR_NUEVO VARCHAR (50) ,
       VALOR_ANT VARCHAR(50),
       FECHA_MOV datetime


)








DECLARE @valor VARCHAR (50)
SET @valor = 'xcvxcvxcvxcv'



UPDATE PASAJERO SET IDPASAJERO = 10 WHERE NOMBRES ='nombre1';

UPDATE PASAJERO SET NOMBRES = 'nomnrenuevo3' WHERE NOMBRES ='nomnrenuevo2';




DROP TRIGGER tr_pasajero_h


SELECT * FROM pasajero
SELECT * FROM pasajero_h
SELECT * FROM DELETED.pasajero

DELETE FROM pasajero_h
DELETE FROM pasajero


INSERT INTO pasajero VALUES (3,'nombre1',3,3,'@gamil')

 DELETE FROM PASAJERO WHERE IDPASAJERO=3

 UPDATE PASAJERO SET IDPAIS = 7 WHERE IDPASAJERO =3;




CREATE TRIGGER tr_pasajero_h

ON PASAJERO
FOR INSERT,DELETE,UPDATE
AS


    IF UPDATE(IDPASAJERO) AND ( EXISTS(SELECT IDPASAJERO FROM inserted pasajero ) AND EXISTS(SELECT IDPASAJERO FROM deleted pasajero ) )
    BEGIN
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'UPDATE','IDPASAJERO',( SELECT TOP 1 IDPASAJERO FROM inserted PASAJERO  ) ,(SELECT TOP 1 IDPASAJERO FROM deleted PASAJERO),Getdate() )
    END

     IF UPDATE(NOMBRES) AND ( EXISTS(SELECT NOMBRES FROM inserted pasajero ) AND EXISTS(SELECT NOMBRES FROM deleted pasajero ) )
    BEGIN
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'UPDATE','NOMBRES',( SELECT TOP 1 NOMBRES FROM inserted PASAJERO  ) ,(SELECT TOP 1 NOMBRES FROM deleted PASAJERO),Getdate() )
    END

       IF UPDATE(IDPAIS) AND ( EXISTS(SELECT IDPAIS FROM inserted pasajero ) AND EXISTS(SELECT IDPAIS FROM deleted pasajero ) )
    BEGIN
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'UPDATE','IDPAIS',( SELECT TOP 1 IDPAIS FROM inserted PASAJERO  ) ,(SELECT TOP 1 IDPAIS FROM deleted PASAJERO),Getdate() )
    END

      IF UPDATE(TELEFONO) AND ( EXISTS(SELECT TELEFONO FROM inserted pasajero ) AND EXISTS(SELECT TELEFONO FROM deleted pasajero ) )
    BEGIN
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'UPDATE','TELEFONO',( SELECT TOP 1 TELEFONO FROM inserted PASAJERO  ) ,(SELECT TOP 1 TELEFONO FROM deleted PASAJERO),Getdate() )
    END

      IF UPDATE(EMAIL) AND ( EXISTS(SELECT EMAIL FROM inserted pasajero ) AND EXISTS(SELECT EMAIL FROM deleted pasajero ) )
    BEGIN
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'UPDATE','EMAIL',( SELECT TOP 1 EMAIL FROM inserted PASAJERO  ) ,(SELECT TOP 1 EMAIL FROM deleted PASAJERO),Getdate() )
    END

 ---------------

    IF EXISTS(SELECT * FROM inserted pasajero ) AND NOT EXISTS(SELECT * FROM deleted pasajero )
    BEGIN
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'INSERT','IDPASAJERO',( SELECT TOP 1 IDPASAJERO FROM inserted PASAJERO  ) ,(SELECT TOP 1 IDPASAJERO FROM deleted PASAJERO),Getdate() )
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'INSERT','NOMBRES',( SELECT TOP 1 NOMBRES FROM inserted PASAJERO  ) ,(SELECT TOP 1 NOMBRES FROM deleted PASAJERO),Getdate() )
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'INSERT','IDPAIS',( SELECT TOP 1 IDPAIS FROM inserted PASAJERO  ) ,(SELECT TOP 1 IDPAIS FROM deleted PASAJERO),Getdate() )
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'INSERT','TELEFONO',( SELECT TOP 1 TELEFONO FROM inserted PASAJERO  ) ,(SELECT TOP 1 TELEFONO FROM deleted PASAJERO),Getdate() )
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'INSERT','EMAIL',( SELECT TOP 1 EMAIL FROM inserted PASAJERO  ) ,(SELECT TOP 1 EMAIL FROM deleted PASAJERO),Getdate() )
    END


    IF EXISTS(SELECT * FROM deleted pasajero) AND NOT EXISTS(SELECT * FROM inserted pasajero)
    BEGIN
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'DELETE','IDPASAJERO',( SELECT TOP 1 IDPASAJERO FROM inserted PASAJERO  ) ,(SELECT TOP 1 IDPASAJERO FROM deleted PASAJERO),Getdate() )
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'DELETE','NOMBRES',( SELECT TOP 1 NOMBRES FROM inserted PASAJERO  ) ,(SELECT TOP 1 NOMBRES FROM deleted PASAJERO),Getdate() )
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'DELETE','IDPAIS',( SELECT TOP 1 IDPAIS FROM inserted PASAJERO  ) ,(SELECT TOP 1 IDPAIS FROM deleted PASAJERO),Getdate() )
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'DELETE','TELEFONO',( SELECT TOP 1 TELEFONO FROM inserted PASAJERO  ) ,(SELECT TOP 1 TELEFONO FROM deleted PASAJERO),Getdate() )
    INSERT INTO pasajero_h VALUES (NEXT VALUE FOR ID_MOV ,'DELETE','EMAIL',( SELECT TOP 1 EMAIL FROM inserted PASAJERO  ) ,(SELECT TOP 1 EMAIL FROM deleted PASAJERO),Getdate() )
    END



    --if insert()

    --








CREATE SEQUENCE ID_MOV
  START WITH 1
  INCREMENT BY 1;