如果想添加的时候自动实现,则需要用触发器,你需要两个触发器 insert / updateCREATE TRIGGER testref BEFORE INSERT ON pwages FOR EACH ROW set new.total_wages=new.Fixed_wage + new.bonus + new.Other_add - new.Other_cut ;CREATE TRIGGER testref BEFORE UPDATE ON pwages FOR EACH ROW set new.total_wages=new.Fixed_wage + new.bonus + new.Other_add - new.Other_cut ;
mysql>create trigger testref before insert on pwages for each row set new.total_wages = new.Fixed_wage + new.bonus + new.other_add + new.other_cut; Query OK,0 rows affected (0.06 sec) mysql>create trigger testref1 before update on pwages for each row set new.total_wages = new.Fixed_wage + new.bonus + new.other_add + new.other_cut; Query OK,0 rows affected (0.06 sec) mysql>insert into pwages values(1,'张三',3500,1000,500,100,null,'2009-09-09 12:12:12','略'); ERROR 1452(23000):Cannot add or update a child row:a foreign key constraint fails('hrms','pwages',constraint 'pwages_ibfk_1' forerign key ('id') references 'employees' ('id'))但, mysql>create table pwages(id char(4),name char(8),Fixed_wage decimal(8,2),bonus decimal(8,2),other_add decimal(8,2),other_cut decimal(8,2),total_wages decimal(8,2),release_date datetime,notes varchar(255));mysql>delimiter $$ mysql>create trigger testref mysql>before insert on pwages for each row mysql>begin mysql>set new.total_wages=new.Fixed_wage + new.bonus + new.Other_add - new.Other_cut ; mysql>end; mysql>$$Query OK,0 rows affected (0.08 sec)mysql>insert into pwages values(1,'张三',3500,1000,500,100,null,'2009-09-09 12:12:12','略'); Query OK,1 row affected (0.03 sec) 在没有外键的情况下,用触发器测试成功,但有外键情况不成功。是不是插入记录按表的顺序来插入?
-- MySQL dump 10.13 Distrib 6.0.10-alpha, for Win32 (ia32) -- -- Host: localhost Database: hrms -- ------------------------------------------------------ -- Server version 6.0.10-alpha-community/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;-- -- Table structure for table `attendance` --DROP TABLE IF EXISTS `attendance`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `attendance` ( `id` char(4) DEFAULT NULL, `name` char(8) DEFAULT NULL, `jiaban` int(11) DEFAULT NULL, `qingjia` int(11) DEFAULT NULL, `kuanggong` int(11) DEFAULT NULL, `date` datetime NOT NULL, PRIMARY KEY (`date`), KEY `id` (`id`), CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employees` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client;-- -- Dumping data for table `attendance` --LOCK TABLES `attendance` WRITE; /*!40000 ALTER TABLE `attendance` DISABLE KEYS */; /*!40000 ALTER TABLE `attendance` ENABLE KEYS */; UNLOCK TABLES;-- -- Table structure for table `depts` --DROP TABLE IF EXISTS `depts`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `depts` ( `id` char(4) NOT NULL, `name` char(10) NOT NULL, `number` int(11) DEFAULT NULL, `leader` char(8) DEFAULT NULL, `Tel` char(15) DEFAULT NULL, PRIMARY KEY (`name`), UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client;-- -- Dumping data for table `depts` --LOCK TABLES `depts` WRITE; /*!40000 ALTER TABLE `depts` DISABLE KEYS */; /*!40000 ALTER TABLE `depts` ENABLE KEYS */; UNLOCK TABLES;-- -- Table structure for table `empchanges` --DROP TABLE IF EXISTS `empchanges`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `empchanges` ( `Order_number` int(11) NOT NULL, `id` char(4) NOT NULL, `ydept` char(10) NOT NULL, `zdept` char(10) NOT NULL, `date` datetime DEFAULT NULL, `notes` varchar(50) DEFAULT NULL, PRIMARY KEY (`Order_number`), KEY `ydept` (`ydept`), KEY `zdept` (`zdept`), CONSTRAINT `empchanges_ibfk_1` FOREIGN KEY (`ydept`) REFERENCES `depts` (`name`), CONSTRAINT `empchanges_ibfk_2` FOREIGN KEY (`zdept`) REFERENCES `depts` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client;-- -- Dumping data for table `empchanges` --LOCK TABLES `empchanges` WRITE; /*!40000 ALTER TABLE `empchanges` DISABLE KEYS */; /*!40000 ALTER TABLE `empchanges` ENABLE KEYS */; UNLOCK TABLES;-- -- Table structure for table `employees` --DROP TABLE IF EXISTS `employees`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `employees` ( `dept_name` char(10) DEFAULT NULL, `id` char(4) NOT NULL, `name` char(8) DEFAULT NULL, `gender` enum('ÄÐ','Å®') NOT NULL, `birth` datetime DEFAULT NULL, `national` char(6) DEFAULT NULL, `Pol` enum('µ³Ô±','ÍÅÔ±','ȺÖÚ') NOT NULL, `Marital` enum('ÒÑ','δ') NOT NULL, `address` varchar(50) DEFAULT NULL, `join_time` enum('Ñо¿Éú','±¾¿Æ','´óר','¸ßÖ°','ÖÐר','ÆäËû') NOT NULL, `profession` enum('½ÌÊÚ','¸±½ÌÊÚ','½²Ê¦','Öú½Ì') NOT NULL, `Tel` char(15) DEFAULT NULL, `ID_number` char(18) DEFAULT NULL, `resume` varchar(200) DEFAULT NULL, `notes` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), KEY `dept_name` (`dept_name`), CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `depts` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client;-- -- Dumping data for table `employees` --LOCK TABLES `employees` WRITE; /*!40000 ALTER TABLE `employees` DISABLE KEYS */; /*!40000 ALTER TABLE `employees` ENABLE KEYS */; UNLOCK TABLES;-- -- Table structure for table `incentive` --DROP TABLE IF EXISTS `incentive`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `incentive` ( `Order_number` int(11) NOT NULL, `id` char(4) DEFAULT NULL, `name` char(8) DEFAULT NULL, `description` char(50) DEFAULT NULL, `type` enum('½±','³Í') DEFAULT NULL, `date` datetime DEFAULT NULL, PRIMARY KEY (`Order_number`), KEY `id` (`id`), CONSTRAINT `incentive_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employees` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client;-- -- Dumping data for table `incentive` --LOCK TABLES `incentive` WRITE; /*!40000 ALTER TABLE `incentive` DISABLE KEYS */; /*!40000 ALTER TABLE `incentive` ENABLE KEYS */; UNLOCK TABLES;-- -- Table structure for table `pwages` --DROP TABLE IF EXISTS `pwages`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `pwages` ( `id` char(4) DEFAULT NULL, `Fixed_wage` decimal(8,2) DEFAULT NULL, `bonus` decimal(8,2) DEFAULT NULL, `other_add` decimal(8,2) DEFAULT NULL, `other_cut` decimal(8,2) DEFAULT NULL, `total_wages` decimal(8,2) DEFAULT NULL, `date` datetime NOT NULL, `notes` varchar(50) DEFAULT NULL, PRIMARY KEY (`date`), KEY `id` (`id`), CONSTRAINT `pwages_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employees` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client;-- -- Dumping data for table `pwages` --LOCK TABLES `pwages` WRITE; /*!40000 ALTER TABLE `pwages` DISABLE KEYS */; /*!40000 ALTER TABLE `pwages` ENABLE KEYS */; UNLOCK TABLES; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = latin1 */ ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger testref before insert on pwages for each row begin set new.total_wages=new.Fixed_wage + new.bonus + new.other_add - new.other_cut; end */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = latin1 */ ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger testref2 before update on pwages for each row begin set new.total_wages=new.Fixed_wage + new.bonus + new.other_add - new.other_cut; end */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ;-- -- Table structure for table `wagefile` --DROP TABLE IF EXISTS `wagefile`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `wagefile` ( `id` char(4) DEFAULT NULL, `name` char(8) DEFAULT NULL, `basic` decimal(8,2) DEFAULT NULL, `post` decimal(8,2) DEFAULT NULL, `subsdies` decimal(8,2) DEFAULT NULL, `fund` decimal(8,2) DEFAULT NULL, KEY `id` (`id`), CONSTRAINT `wagefile_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employees` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; SET character_set_client = @saved_cs_client;-- -- Dumping data for table `wagefile` --LOCK TABLES `wagefile` WRITE; /*!40000 ALTER TABLE `wagefile` DISABLE KEYS */; /*!40000 ALTER TABLE `wagefile` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2009-11-29 8:18:58
然后,怎么添加代码,使得total_wages=Fixed_wage + bonus + Other_add - Other_cut 在表中添加,不是在查询时计算。
http://topic.csdn.net/u/20091127/16/278761a5-7a37-4f75-8955-0b6eb50c0f2a.html
set 应发工资=固定工资+奖金+额外金-扣除
FOR EACH ROW
set new.total_wages=new.Fixed_wage + new.bonus + new.Other_add - new.Other_cut ;CREATE TRIGGER testref BEFORE UPDATE ON pwages
FOR EACH ROW
set new.total_wages=new.Fixed_wage + new.bonus + new.Other_add - new.Other_cut ;
Query OK,0 rows affected (0.06 sec)
mysql>create trigger testref1 before update on pwages for each row set new.total_wages = new.Fixed_wage + new.bonus + new.other_add + new.other_cut;
Query OK,0 rows affected (0.06 sec)
mysql>insert into pwages values(1,'张三',3500,1000,500,100,null,'2009-09-09 12:12:12','略');
ERROR 1452(23000):Cannot add or update a child row:a foreign key constraint fails('hrms','pwages',constraint 'pwages_ibfk_1' forerign key ('id') references 'employees' ('id'))但,
mysql>create table pwages(id char(4),name char(8),Fixed_wage decimal(8,2),bonus decimal(8,2),other_add decimal(8,2),other_cut decimal(8,2),total_wages decimal(8,2),release_date datetime,notes varchar(255));mysql>delimiter $$
mysql>create trigger testref
mysql>before insert on pwages for each row
mysql>begin
mysql>set new.total_wages=new.Fixed_wage + new.bonus + new.Other_add - new.Other_cut ;
mysql>end;
mysql>$$Query OK,0 rows affected (0.08 sec)mysql>insert into pwages values(1,'张三',3500,1000,500,100,null,'2009-09-09 12:12:12','略');
Query OK,1 row affected (0.03 sec)
在没有外键的情况下,用触发器测试成功,但有外键情况不成功。是不是插入记录按表的顺序来插入?
--
-- Host: localhost Database: hrms
-- ------------------------------------------------------
-- Server version 6.0.10-alpha-community/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;--
-- Table structure for table `attendance`
--DROP TABLE IF EXISTS `attendance`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `attendance` (
`id` char(4) DEFAULT NULL,
`name` char(8) DEFAULT NULL,
`jiaban` int(11) DEFAULT NULL,
`qingjia` int(11) DEFAULT NULL,
`kuanggong` int(11) DEFAULT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`date`),
KEY `id` (`id`),
CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;--
-- Dumping data for table `attendance`
--LOCK TABLES `attendance` WRITE;
/*!40000 ALTER TABLE `attendance` DISABLE KEYS */;
/*!40000 ALTER TABLE `attendance` ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table `depts`
--DROP TABLE IF EXISTS `depts`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `depts` (
`id` char(4) NOT NULL,
`name` char(10) NOT NULL,
`number` int(11) DEFAULT NULL,
`leader` char(8) DEFAULT NULL,
`Tel` char(15) DEFAULT NULL,
PRIMARY KEY (`name`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;--
-- Dumping data for table `depts`
--LOCK TABLES `depts` WRITE;
/*!40000 ALTER TABLE `depts` DISABLE KEYS */;
/*!40000 ALTER TABLE `depts` ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table `empchanges`
--DROP TABLE IF EXISTS `empchanges`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `empchanges` (
`Order_number` int(11) NOT NULL,
`id` char(4) NOT NULL,
`ydept` char(10) NOT NULL,
`zdept` char(10) NOT NULL,
`date` datetime DEFAULT NULL,
`notes` varchar(50) DEFAULT NULL,
PRIMARY KEY (`Order_number`),
KEY `ydept` (`ydept`),
KEY `zdept` (`zdept`),
CONSTRAINT `empchanges_ibfk_1` FOREIGN KEY (`ydept`) REFERENCES `depts` (`name`),
CONSTRAINT `empchanges_ibfk_2` FOREIGN KEY (`zdept`) REFERENCES `depts` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;--
-- Dumping data for table `empchanges`
--LOCK TABLES `empchanges` WRITE;
/*!40000 ALTER TABLE `empchanges` DISABLE KEYS */;
/*!40000 ALTER TABLE `empchanges` ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table `employees`
--DROP TABLE IF EXISTS `employees`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `employees` (
`dept_name` char(10) DEFAULT NULL,
`id` char(4) NOT NULL,
`name` char(8) DEFAULT NULL,
`gender` enum('ÄÐ','Å®') NOT NULL,
`birth` datetime DEFAULT NULL,
`national` char(6) DEFAULT NULL,
`Pol` enum('µ³Ô±','ÍÅÔ±','ȺÖÚ') NOT NULL,
`Marital` enum('ÒÑ','δ') NOT NULL,
`address` varchar(50) DEFAULT NULL,
`join_time` enum('Ñо¿Éú','±¾¿Æ','´óר','¸ßÖ°','ÖÐר','ÆäËû') NOT NULL,
`profession` enum('½ÌÊÚ','¸±½ÌÊÚ','½²Ê¦','Öú½Ì') NOT NULL,
`Tel` char(15) DEFAULT NULL,
`ID_number` char(18) DEFAULT NULL,
`resume` varchar(200) DEFAULT NULL,
`notes` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `dept_name` (`dept_name`),
CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `depts` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;--
-- Dumping data for table `employees`
--LOCK TABLES `employees` WRITE;
/*!40000 ALTER TABLE `employees` DISABLE KEYS */;
/*!40000 ALTER TABLE `employees` ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table `incentive`
--DROP TABLE IF EXISTS `incentive`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `incentive` (
`Order_number` int(11) NOT NULL,
`id` char(4) DEFAULT NULL,
`name` char(8) DEFAULT NULL,
`description` char(50) DEFAULT NULL,
`type` enum('½±','³Í') DEFAULT NULL,
`date` datetime DEFAULT NULL,
PRIMARY KEY (`Order_number`),
KEY `id` (`id`),
CONSTRAINT `incentive_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;--
-- Dumping data for table `incentive`
--LOCK TABLES `incentive` WRITE;
/*!40000 ALTER TABLE `incentive` DISABLE KEYS */;
/*!40000 ALTER TABLE `incentive` ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table `pwages`
--DROP TABLE IF EXISTS `pwages`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `pwages` (
`id` char(4) DEFAULT NULL,
`Fixed_wage` decimal(8,2) DEFAULT NULL,
`bonus` decimal(8,2) DEFAULT NULL,
`other_add` decimal(8,2) DEFAULT NULL,
`other_cut` decimal(8,2) DEFAULT NULL,
`total_wages` decimal(8,2) DEFAULT NULL,
`date` datetime NOT NULL,
`notes` varchar(50) DEFAULT NULL,
PRIMARY KEY (`date`),
KEY `id` (`id`),
CONSTRAINT `pwages_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;--
-- Dumping data for table `pwages`
--LOCK TABLES `pwages` WRITE;
/*!40000 ALTER TABLE `pwages` DISABLE KEYS */;
/*!40000 ALTER TABLE `pwages` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger testref before insert on pwages for each row
begin
set new.total_wages=new.Fixed_wage + new.bonus + new.other_add - new.other_cut;
end */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger testref2 before update on pwages for each row
begin
set new.total_wages=new.Fixed_wage + new.bonus + new.other_add - new.other_cut;
end */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;--
-- Table structure for table `wagefile`
--DROP TABLE IF EXISTS `wagefile`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `wagefile` (
`id` char(4) DEFAULT NULL,
`name` char(8) DEFAULT NULL,
`basic` decimal(8,2) DEFAULT NULL,
`post` decimal(8,2) DEFAULT NULL,
`subsdies` decimal(8,2) DEFAULT NULL,
`fund` decimal(8,2) DEFAULT NULL,
KEY `id` (`id`),
CONSTRAINT `wagefile_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;--
-- Dumping data for table `wagefile`
--LOCK TABLES `wagefile` WRITE;
/*!40000 ALTER TABLE `wagefile` DISABLE KEYS */;
/*!40000 ALTER TABLE `wagefile` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2009-11-29 8:18:58