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