Write 3 small triggers for Mysql

Result must work from Mysql 5.1 and above. I have three small databases (attached). I need 3 triggers (insert, delete, update).

The databases:

(1) casedocs. This is the main target for the triggers.

(2) mydocs. This is from where all the triggers are initiated.

(3) doctypes. Joined to mydocs. One value needs to be coppy/insert from here

The triggers

(1) Insert (after):

mydocs:

-Set created datetime in [url removed, login to view]

casedocs:

- new row in casedocs

- set created datetime in [url removed, login to view]

- copy [url removed, login to view] to [url removed, login to view]

- copy [url removed, login to view] to [url removed, login to view]

- copy [url removed, login to view] to [url removed, login to view]

- copy [url removed, login to view] to [url removed, login to view]

- by join (mydocs.iddoctype=[url removed, login to view]) copy [url removed, login to view] to [url removed, login to view]

(2) Delete

Delete the corrsponding row in casedocs

(3) Update:

- Check if corresponding casedocs exist, if not create it and perform the insert statements

- Update [url removed, login to view]

- Update [url removed, login to view]

check if [url removed, login to view] is alterd, if so copy new value to [url removed, login to view]

Also, make the code easy to read, with comments for later modification.

Edit:

Get the table-name of mydocs (can variate) and put into casedocs.table

The upload files wont work so i put the database here:

--
-- Script was generated by Devart dbForge Studio for MySQL, Version 7.1.29.0
-- Product home page: http://www.devart.com/dbforge/mysql/studio
-- Script date 2016-11-15 04:46:18
-- Server version: 5.5.45
-- Client version: 4.1
--


--
-- Disable foreign keys
--
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

--
-- Set SQL mode
--
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

--
-- Set character set the client will use to send SQL statements to the server
--
SET NAMES 'utf8';

--
-- Set default database
--
USE casetrigger;

--
-- Definition for table casedocs
--
DROP TABLE IF EXISTS casedocs;
CREATE TABLE casedocs (
id INT(11) DEFAULT NULL,
`table` VARCHAR(255) DEFAULT NULL,
idintable INT(11) DEFAULT NULL,
`case` INT(11) DEFAULT NULL,
doctypeid INT(11) DEFAULT NULL,
doctype VARCHAR(255) DEFAULT NULL COMMENT 'Vilken dokumenttyp',
docdate DATETIME DEFAULT NULL,
mycreated DATETIME DEFAULT NULL,
myupdated DATETIME DEFAULT NULL
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;

--
-- Definition for table doctypes
--
DROP TABLE IF EXISTS doctypes;
CREATE TABLE doctypes (
iddoctype INT(11) NOT NULL AUTO_INCREMENT,
placement INT(11) DEFAULT NULL,
nbrNext INT(11) DEFAULT NULL,
typename VARCHAR(50) DEFAULT NULL,
ifDept TINYINT(1) DEFAULT 0,
ifPayment TINYINT(1) DEFAULT 0,
PRIMARY KEY (iddoctype)
)
ENGINE = MYISAM
AUTO_INCREMENT = 10
AVG_ROW_LENGTH = 1820
CHARACTER SET utf8
COLLATE utf8_general_ci;

--
-- Definition for table mydocs
--
DROP TABLE IF EXISTS mydocs;
CREATE TABLE mydocs (
id INT(11) NOT NULL AUTO_INCREMENT,
`case` INT(11) DEFAULT NULL,
iddoctype INT(11) DEFAULT NULL,
mycreated DATETIME DEFAULT NULL,
myupdated DATETIME DEFAULT NULL,
PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8
COLLATE utf8_general_ci;

--
-- Dumping data for table casedocs
--

-- Table casetrigger.casedocs does not contain any data (it is empty)

--
-- Dumping data for table doctypes
--
INSERT INTO doctypes VALUES
(1, 11, 16003, 'Quotation', 0, 0),
(2, 3, 16300, 'Order', 0, 0),
(3, 4, 16002, 'OrderConfirmation', 0, 0),
(4, 5, 16295, 'Invoice', 1, 0),
(5, 6, 16304, 'Tax Invoice/Receipt', 0, 1),
(6, 7, 16003, 'Tax Invoice/Invoice', 1, 0),
(7, 8, 16005, 'Receipt', 0, 1),
(8, 9, 16001, 'DelevieryNote', 0, 0),
(9, 10, 16001, 'WorkOrder', 0, 0);

--
-- Dumping data for table mydocs
--

-- Table casetrigger.mydocs does not contain any data (it is empty)

--
-- Restore previous SQL mode
--
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

--
-- Enable foreign keys
--
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

MySQL

プロジェクトID: #12078894

プロジェクトについて