From 9fe033ea88c2f705ec18c232873d056e0c229d72 Mon Sep 17 00:00:00 2001
From: Gaspard Coulet <gaspard.coulet@mines-ales.org>
Date: Wed, 28 Apr 2021 23:05:53 +0200
Subject: Initial commit

---
 sem_5/HLIN511_BDD/trigger0.sql | 92 ++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 92 insertions(+)
 create mode 100644 sem_5/HLIN511_BDD/trigger0.sql

(limited to 'sem_5/HLIN511_BDD/trigger0.sql')

diff --git a/sem_5/HLIN511_BDD/trigger0.sql b/sem_5/HLIN511_BDD/trigger0.sql
new file mode 100644
index 0000000..caef8e2
--- /dev/null
+++ b/sem_5/HLIN511_BDD/trigger0.sql
@@ -0,0 +1,92 @@
+set serveroutput on
+
+
+
+
+create or replace trigger trigger_1
+before insert on emprunt
+for each row
+begin
+dbms_output.put_line('insertion du tuple '||:new.numero);
+end;
+/
+
+
+select trigger_name from user_triggers;
+drop trigger trigger1;
+
+show errors
+
+insert into exemplaire values (5200,'18-OCT-93',40,'EMPRUNTABLE','BON','0_18_47892_2');
+
+select trim(to_char(sysdate,'day')) from dual;
+
+
+
+
+
+create or replace trigger trigger_2
+before insert on exemplaire
+for each row
+begin
+if trim(to_char(sysdate,'day')) = 'monday'
+then raise_application_error(-20100,'insertion impossible le lundi');
+end if ;
+end;
+/
+
+insert into exemplaire values (5201,'18-OCT-93',40,'EMPRUNTABLE','BON','0_18_47892_2');
+
+drop trigger trigger_2;
+
+create table t1 (numero number(4), date_insertion date, usager varchar(20), typeOperation varchar(12));
+
+create or replace trigger trigger_3
+after insert or update or delete on exemplaire
+for each row
+begin
+if inserting
+then
+insert into t1 values (:new.numero,sysdate,user,'insertion');
+elsif updating
+then
+insert into t1 values (:new.numero,sysdate,user,'modification');
+elsif deleting
+then
+insert into t1 values (:old.numero,sysdate,user,'suppression');
+end if;
+end;
+/
+
+
+insert into exemplaire values (6202,'18-OCT-93',40,'EMPRUNTABLE','BON','0_18_47892_2');
+
+
+update exemplaire set prix = 100 where numero = 6202;
+
+delete from exemplaire where numero =6202;
+
+-- programme principal
+-- curseur implicite
+declare
+num number(4);
+isbn_livre varchar(20);
+begin
+select numero, isbn into num, isbn_livre from exemplaire where numero =&num;
+dbms_output.put_line(' exemplaire '||num||'  '||isbn_livre);
+exception when no_data_found then dbms_output.put_line('inexistant');
+end;
+/
+
+
+-- curseur explicite
+declare
+cursor c is select numero, isbn from exemplaire;
+begin
+for c_t in c
+loop
+dbms_output.put_line(' exemplaire '||c_t.numero||'  '||c_t.isbn);
+end loop;
+exception when no_data_found then dbms_output.put_line('inexistant');
+end;
+/
-- 
cgit v1.2.3