implement audit log + triggers

4 files changed, 91 insertions, 1 deletions

diff --git a/dbinit/init.sql b/dbinit/init.sql
index c468c9c..e206a7d 100644
--- a/dbinit/init.sql
+++ b/dbinit/init.sql
@@ -201,5 +201,15 @@ create table if not exists `formula1`.`teamsmember` (
     foreign key (`teamsID`)
     references `formula1`.`teams` (`ID`));
 
+create table if not exists `formula1`.`auditlog` (
+  `ID` int not null auto_increment,
+	`timestamp` timestamp not null default current_timestamp(),
+	`user` varchar(45) not null default user(),
+	`action` varchar(10) not null,
+	`fieldName` varchar(45) not null,
+	`newData` varchar(45) null,
+  primary key (`ID`),
+  unique index `ID_UNIQUE` (`ID` asc) visible);
+
 use `formula1`;	
 
diff --git a/dbinit/makefile b/dbinit/makefile
index 76f867f..f2dcd75 100644
--- a/dbinit/makefile
+++ b/dbinit/makefile
@@ -25,7 +25,7 @@ full: full.sql
 base.sql: reset.sql init.sql
 	cat $^ > $@
 
-full.sql: reset.sql init.sql demo.sql views.sql functions.sql procedures.sql
+full.sql: reset.sql init.sql demo.sql views.sql functions.sql procedures.sql triggers.sql
 	cat $^ > $@
 
 data.sql:
diff --git a/dbinit/procedures.sql b/dbinit/procedures.sql
index d94ea65..776a7ca 100644
--- a/dbinit/procedures.sql
+++ b/dbinit/procedures.sql
@@ -35,3 +35,4 @@ create procedure spUpdatePersons(imgPath varchar(255))
 			where `B`.`ID` = `A`.`ID`);
 	end$$
 delimiter ;
+
diff --git a/dbinit/triggers.sql b/dbinit/triggers.sql
new file mode 100644
index 0000000..4acc242
--- /dev/null
+++ b/dbinit/triggers.sql
@@ -0,0 +1,79 @@
+drop trigger if exists endposition_ai;
+drop trigger if exists endposition_ad;
+drop trigger if exists endposition_au;
+drop trigger if exists fastestlap_ai;
+drop trigger if exists fastestlap_ad;
+drop trigger if exists fastestlap_au;
+
+delimiter $$
+create trigger endposition_ai after insert on endposition
+for each row begin
+	insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`)
+		select 'insert', 'endposition.memberID', cast(new.`memberID` as char)
+		from `endposition` as d where d.`ID` = new.`ID`;
+	insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`)
+		select 'insert', 'endposition.position', cast(new.`position` as char)
+		from `endposition` as d where d.`ID` = new.`ID`;
+	insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`)
+		select 'insert', 'endposition.specialPositionID', cast(new.`specialPositionID` as char)
+		from `endposition` as d where d.`ID` = new.`ID`;
+end; $$
+delimiter ;
+
+delimiter $$
+create trigger endposition_ad before delete on endposition
+for each row begin
+	insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`)
+		select 'delete', 'endposition.memberID', NULL
+		from `endposition` as d where d.`ID` = old.`ID`;
+	insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`)
+		select 'delete', 'endposition.position', NULL
+		from `endposition` as d where d.`ID` = old.`ID`;
+	insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`)
+		select 'delete', 'endposition.specialPositionID', NULL
+		from `endposition` as d where d.`ID` = old.`ID`;
+end; $$
+delimiter ;
+
+delimiter $$
+create trigger endposition_au after update on endposition
+for each row begin
+	insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`)
+		select 'update', 'endposition.memberID', cast(new.`memberID` as char)
+		from `endposition` as d where d.`ID` = new.`ID` and old.`memberID` != new.`memberID`;
+	insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`)
+		select 'update', 'endposition.position', cast(new.`position` as char)
+		from `endposition` as d where d.`ID` = new.`ID` and old.`position` != new.`position`;
+	insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`)
+		select 'update', 'endposition.specialPositionID', cast(new.`specialPositionID` as char)
+		from `endposition` as d where d.`ID` = new.`ID` and old.`specialPositionID` != new.`specialPositionID`;
+end; $$
+delimiter ;
+
+
+delimiter $$
+create trigger fastestlap_ai after insert on fastestlap
+for each row begin
+	insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`)
+		select 'insert', 'fastestlap.memberID', cast(new.`memberID` as char)
+		from `fastestlap` as d where d.`ID` = new.`ID`;
+end; $$
+delimiter ;
+
+delimiter $$
+create trigger fastestlap_ad before delete on fastestlap
+for each row begin
+	insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`)
+		select 'delete', 'fastestlap.memberID', NULL
+		from `fastestlap` as d where d.`ID` = old.`ID`;
+end; $$
+delimiter ;
+
+delimiter $$
+create trigger fastestlap_au after update on fastestlap
+for each row begin
+	insert into `formula1`.`auditlog` (`action`, `fieldName`, `newData`)
+		select 'update', 'fastestlap.memberID', cast(new.`memberID` as char)
+		from `fastestlap` as d where d.`ID` = new.`ID` and old.`memberID` != new.`memberID`;
+end; $$
+delimiter ;