PL/SQL. Trigger 3

Ejercicio 1.

Una empresa almacena los datos de sus empleados en una tabla denominada "empleados" y en otra tabla llamada "secciones", el código de la sección y el sueldo máximo de cada una de ellas.

1. Elimine las tablas si existen

2. Cree las tablas

3. Ingrese algunos registros en ambas tablas

4. Cree un disparador para que se ejecute cada vez que una instrucción "insert" ingrese datos en "empleados"; el mismo debe verificar que el sueldo del empleado no sea mayor al sueldo máximo establecido para la sección, si lo es, debe mostrar un mensaje indicando tal situación y deshacer la transacción.

5. Ingrese un nuevo registro en "empleados" cuyo sueldo sea menor o igual al establecido para la sección.

6. Verifique que el disparador se ejecutó consultando la tabla "empleados":  select *from empleados

7. Intente ingresar un nuevo registro en "empleados" cuyo sueldo sea mayor al establecido para la sección. El disparador se ejecutó mostrando un mensaje y la transacción se deshizo.

8. Intente ingresar un empleado con código de sección inexistente. Aparece un mensaje de error porque se viola la restricción "foreign key"; el trigger no llegó a  ejecutarse.

1.1.

 if object_id('empleados') is not null

                drop table empleados;

if object_id('secciones') is not null

                drop table secciones;

1.2

create table secciones(

  codigo int identity,

  nombre varchar(30),

  sueldomaximo decimal(8,2),

  constraint PK_secciones primary key(codigo)

 );

 create table empleados(

  documento char(8) not null,

  nombre varchar(30) not null,

  domicilio varchar(30),

  codigoseccion int not null,

  sueldo decimal(8,2),

  constraint PK_empleados primary key(documento),

  constraint FK_empelados_seccion

   foreign key (codigoseccion) references secciones(codigo)

 );

1.3

insert into secciones values('Administracion',1500);

 insert into secciones values('Sistemas',2000);

 insert into secciones values('Secretaria',1000);

 insert into empleados values('22222222','Ana Acosta','Avellaneda 88',1,1100);

 insert into empleados values('23333333','Bernardo Bustos','Bulnes 345',1,1200);

 insert into empleados values('24444444','Carlos Caseres','Colon 674',2,1800);

 insert into empleados values('25555555','Diana Duarte','Colon 873',3,1000);

1.4

create trigger dis_empleados_insertar

  on empleados

  for insert

 as

  declare @maximo decimal(8,2)

  set @maximo=(select sueldomaximo from secciones

              join inserted

              on inserted.codigoseccion=secciones.codigo)

  if (@maximo<(select sueldo from inserted))

  begin

    declare @mensaje varchar(40)

    set @mensaje='El sueldo debe ser menor a '+cast(@maximo as char(8))

    raiserror(@mensaje, 16, 1)

    rollback transaction

  end;

1.5,6,7,8

—  R: 5.: select *from empleados;

—  R: 6. insert into empleados values('26666666','Federico Fuentes','Francia 938',2,1000);

—  R: 7. insert into empleados values('27777777','Gaston Garcia','Guemes 366',3,1200);

—  R:8. select *from empleados; insert into empleados values('27777777','Gaston Garcia','Guemes 366',9,1200);

Ejercicio 2

Un club de barrio almacena los datos de sus socios en una tabla llamada "socios", los datos de las inscripciones en una tabla denominada "inscriptos" y en una tabla "morosos" almacena el documento de los socios inscriptos que deben matrícula.

1.Cree un disparador de inserción que no permita ingresar inscripciones si el socio es moroso.

2.Cree otro "insert trigger" para "inscriptos" que ingrese el socio en la tabla "morosos" si no paga la matrícula (si se ingresa 'n' para el campo "matricula"). Recuerde que podemos crear varios triggers para un mismo evento sobre una misma tabla

3. Creamos un disparador sobre la tabla "socios" para que no permita ingresar nuevos socios. El mismo debe mostrar un mensaje al dispararse y deshacer la transacción.

Estructura tablas

Cree las tablas, con las siguientes estructuras:

 create table socios(

  documento char(8) not null,

  nombre varchar(30),

  domicilio varchar(30),

  constraint PK_socios primary key (documento)

 );

create table inscriptos(

  documento char(8) not null,

  deporte varchar(30) not null,

  matricula char(1),

  constraint CK_inscriptos check (matricula in ('s','n')),

  constraint PK_inscriptos primary key (documento,deporte),

  constraint FK_inscriptos_documento foreign key(documento)

   references socios (documento)

 );

 create table morosos(

  documento char(8) not null

 );

insert into socios values ('22222222','Ana Acosta','Avellaneda 800');

 insert into socios values ('23333333','Bernardo Bustos','Bulnes 234');

 insert into socios values ('24444444','Carlos Caseros','Colon 321');

 insert into socios values ('25555555','Mariana Morales','Maipu 483');

 insert into inscriptos values ('22222222','tenis','s');

 insert into inscriptos values ('22222222','natacion','n');

 insert into inscriptos values ('23333333','tenis','n');

 insert into inscriptos values ('24444444','tenis','s');

 insert into inscriptos values ('24444444','futbol','s');

 insert into morosos values ('22222222');

 insert into morosos values ('23333333');

2.1

create trigger dis_incriptos_insertar

  on inscriptos

  for insert

  as

   declare @doc char(8)

   select @doc= documento from inserted

   if exists (select *from morosos where documento=@doc)

   begin

    raiserror('No puede inscribir al socio porque es moroso', 16, 1)

    rollback transaction

   end;

2.2

create trigger dis_incriptos_insertar2

  on inscriptos

  for insert

  as

   if (select matricula from inserted)='n'

    insert into morosos select documento from inserted;

2.3

create trigger dis_socios

  on socios

  for insert

  as

   raiserror('No puede ingresar nuevos socios', 16, 1)

   rollback transaction;