diff options
| -rw-r--r-- | dbinit/init.sql | 10 | ||||
| -rw-r--r-- | dbinit/makefile | 2 | ||||
| -rw-r--r-- | dbinit/procedures.sql | 1 | ||||
| -rw-r--r-- | dbinit/triggers.sql | 79 |
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 ; |