Scribd
Upload
23 views5 pages

Taller SQL

The document discusses creating triggers for the Instructor, Takes, and Teaches tables in a class project database. The triggers are created to log the username of whoever performs an insert, update, or delete action on those tables to another log table. Examples are provided for creating the triggers and functions for each of the tables and testing the triggers by performing insert, update, and delete actions and checking the log table.

Uploaded by

Yonny Cuesta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
23 views5 pages

Taller SQL

The document discusses creating triggers for the Instructor, Takes, and Teaches tables in a class project database. The triggers are created to log the username of whoever performs an insert, update, or delete action on those tables to another log table. Examples are provided for creating the triggers and functions for each of the tables and testing the triggers by performing insert, update, and delete actions and checking the log table.

Uploaded by

Yonny Cuesta
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

TALLER

Generar triggers para las siguientes tablas en el proyecto de clase:


Instructor
Takes
Teaches
Que almacene el nombre del usuario que ha realizado:
Insert
Update
Delete

SOLUCIÓN
UPDATE (TABLE TAKES)-----1
--FUNCIÓN CREADA
create function SP1_Test() returns Trigger
as
$$
begin
insert into muestra_log values
(old.id,old.course_id,old.sec_id,old.semester,old.year,old.grade);
return new;
end
$$
language plpgsql;
--TRIGGER CREADO
create trigger TR1_update before update on takes
for each row
execute procedure SP1_Test();

--ACTUALIZACIÓN
update takes set
grade='B+'
where year =2003;

--CONSULTAS
select*from takes
select *from takes where year =2003;
select*from muestra_log

DELETE (TAKES)
--TRIGGER CREADO
create trigger TR11_update after delete on takes
for each row
execute procedure SP1_Test();
--DELETE
delete from takes
where id='61332';

--CONSULTAS
select *from takes where id='61332'
select*from muestra_log
select*from muestra_log where id='61332'

INSERT (TAKES)
--TRIGGER CREADO
create trigger TR2_insert after insert on takes
for each row
execute procedure SP3_Test();

--INSERT
insert into takes
values ('74016','760','1','Spring','2004','BB')

--CONSULTAS
select *from takes where id='74016'
select*from muestra_log
select*from muestra_log where id='74016'

UPDATE (TABLE INSTRUCTOR) ------2


--FUNCIÓN CREADA
create function SP3_Test() returns Trigger
as
$$
begin
insert into muestra_log values (old.id);
return new;
end
$$
language plpgsql;
--TRIGGER CREADO
create trigger TR3_update before update on instructor
for each row
execute procedure SP3_Test();

--ACTUALIZACIÓN
update instructor set
name='Yonny'
where id ='78699';

--CONSULTAS
select*from instructor
select *from instructor where id ='78699';
select*from muestra_log where id='78699';

DELETE (INSTRUCTOR)
--TRIGGER CREADO
create trigger TR2_delete after delete on instructor
for each row
execute procedure SP3_Test();
--DELETE
delete from instructor
where id='50885';

--CONSULTAS
select *from instructor where id='50885'
select*from muestra_log
select*from muestra_log where id='50885'

INSERT (INSTRUCTOR)
--TRIGGER CREADO
create trigger TR2_insert after insert on instructor
for each row
execute procedure SP3_Test ();

--INSERT
insert into instructor
values ('74016','Cuesta','Cybernetics',100356.45)

--CONSULTAS
select *from instructor where id='74016'
select*from muestra_log
select*from muestra_log where id='74016'

DELETE (TABLE TEACHES) -----3


--FUNCION CREADA
create function SP2_Test() returns Trigger
as
$$
begin
insert into muestra_log values (old.id);
return new;
end
$$
language plpgsql;

--TRIGGER CREADO
create trigger TR2_delete after delete on teaches
for each row
execute procedure SP2_Test ();
--DELETE
delete from teaches
where semester='Fall';
--CONSULTAS
select *from teaches where semester='Fall'
select*from muestra_log
select*from muestra_log where semester='Fall'

UPDATE (TEACHES)
--FUNCION CREADA
create function SP2_Test() returns Trigger
as
$$
begin
insert into muestra_log values (old.id);
return new;
end
$$
language plpgsql;

--TRIGGER CREADO
create trigger TR2_update after update on teaches
for each row
execute procedure SP2_Test();

--UPDATE
update teaches set
course_id='109',
sec_id='2',
semester='Fall',
year=2004
where id ='22591';

--CONSULTAS
select *from teaches where id='659'
select*from muestra_log
select*from muestra_log where id='659'

INSERT (TEACHES)
--Funcion creada
create function SP211_Test() returns Trigger
as
$$
begin
insert into muestra_log values (old.id);
return new;
end
$$
language plpgsql;

--TRIGGER CREADO
create trigger TR2_insert after insert on teaches
for each row
execute procedure SP2_Test ();

--INSERT
insert into teaches
values ('74016','974','1','Fall',2003)

--CONSULTAS
select *from section
select * from teaches
select *from teaches where id='74016'
select*from muestra_log
select*from muestra_log where id='74016'

You might also like