Bitacora of a database?

0

I need to create a trigger that registers the insert, update, delete that happen in a certain table. For this, I have the following code in which, within the trigger for insert that I already have defined, I miss an error in saying that the name of the column "cveMov" is not valid. Also, I would like to know if there are any recommendations that you can give me for the other triggers, for insert and delete

    /**Creando la base de datos Empresa*/
    create database Empresa
    go

    use Empresa
    go

    /*Creando las tablas de la base de datos*/
    create table Cargo(
    cveCargo smallint not null identity constraint cargo_pk primary key,
    cargo varchar(50) not null,
    salarioBase int not null
    )
    go

    create table Empleado(
    cveEmp smallint not null identity constraint empleado_pk primary key,
    nombres varchar(100) not null,
    apellidoPat varchar(50) not null,
    apellidoMat varchar(50) not null,
    email varchar(80) not null
    )
    go

    create table CargoEmpleado(
    cveCargo smallint not null constraint cveCargo_fk references Cargo(cveCargo),
    cveEmp smallint not null constraint cveEmp_fk references Empleado(cveEmp),
    constraint CargoEmpleado_pk primary key (cveCargo,cveEmp)
    )
    go


    /*CREACIÓN DE LA BASE DE DATOS PARA LA BITACORA DE MOVIMIENTOS*/
    create database Bitacora
    go

    use Bitacora
    go

    /*CREANDO LA TABLA PRA LA BITACORA DE MOVIMIENTOS*/
    create table Bitacora(
    cveMov int not null identity constraint bitacora_pk primary key, 
    fechaHora datetime not null,
    tipoMovimiento char(6) not null,
    tablaAfectada varchar(15) not null,
    usuario varchar(25) not null,
    host varchar(15) not null
    )
    go

    create table BitacoraDetalle(
    cveMov int not null constraint BitacoraDetalle_cveMov_FK references Bitacora(cveMov),
    cveMovDeta int not null identity,
    cveModificada int not null,
    constraint BitacoraDetalle_PK primary key (cveMov,cveModificada,cveMovDeta),
    campoAlterado varchar (50) not null,
    valorAnterior varchar (150) null,
    valorNuevo varchar (150) null
    )
    go

    /*CREANDO EL DISPARADOR*/
    Create Trigger tri_InsertCargo
    On Cargo
    After Insert
        As
    declare @fechahora datetime = getdate()
    declare @host varchar(50) =host_name()
    declare @usuario varchar(50) = system_user
    declare @movimiento char(6) = 'Insert'
    declare @tabla varchar(10) = 'Cargo'

    Insert into dbo.Bitacora (fechaHora,tipoMovimiento,tablaAfectada,usuario,host)
    Values (@fechahora,@movimiento,@tabla,@usuario,@host)

    declare @atributo1 varchar(15) ='cargo'
    declare @atributo2 varchar(15) ='salarioBase'
    declare @cargo varchar(50) = null
    declare @cargo1 varchar(50) = (Select cargo From INSERTED)
    declare @sueldo int = null
    declare @sueldo1 int = (Select salarioBase From INSERTED)
    declare @claveModificada smallint = (Select cveCargo From INSERTED)
    declare @cveMov int = (Select cveMov from INSERTED)

    Insert into dbo.BitacoraDetalle(cveMov,cveModificada,campoAlterado,valorAnterior,valorNuevo)
    Values (@cveMov,@claveModificada,@atributo1,@cargo,@cargo1)

    Insert into dbo.BitacoraDetalle (cveMov,cveModificada,campoAlterado,valorAnterior,valorNuevo)
    Values (@cveMov,@claveModificada,@atributo2,@sueldo,@sueldo1)
    Go

Thanks

    
asked by Felipe Flores 01.11.2016 в 20:32
source

2 answers

1

The trigger is defined for the Cargo table. So you can only access the fields defined in that table when you reference the temporary table inserted . Since table Cargo does not have field cveMov , that's why it gives you an error when trying to access Select cveMov from INSERTED

    
answered by 01.11.2016 в 20:35
0

There is a way to take data from another table using an INNER JOIN but I have done it for what is postgresql and it is like this

DECLARE BEGIN
INSERT INTO public.logbook (boxnum, entrydatem, exitdatem, partnum) SELECT old.boxnumm, old.entrydate, old.exitdate, partnum
FROM public.market me INNER JOIN public.connection cp ON me.boxnumm = cp.boxnum
where cp.boxnum = old.boxnumm;
RETURN OLD;
END;
$insertar$ LANGUAGE plpgsql;

You could try to do it for what you want

    
answered by 12.01.2018 в 23:28