数据库开发技术触发器有关解答

1.创建触发器

例题:插入或修改工资表

DELIMITER $ CREATE

TRIGGER `staff`.`beforeinsert_payloyee` BEFORE INSERT 

ON `staff`.`payroll`

FOR EACH ROW BEGIN set new.`actualSalary`=new.`baseSalary`+new.`bonus`-new.`deductMoney`;

END$$ DELIMITER ;

 2.建立索引

例题:在员工表中依据姓名userName建立索引。

CREATE INDEX index_userName ON `employee`(`userName`);

3.创建视图

例题:建立员工部门工资视图

CREATE VIEW name

AS

SELECT `userName`,`deptName`,`baseSalary`,`actualSalary`,`bonus`,`deductMoney`

FROM`dept`,`employee`,`payroll`

WHERE `dept`.`id`=`employee`.`deptId` AND `employee`.`id`=`payroll`.`empId`

4.触发器运用

例题:利用触发器实现插入请假信息时，审核人编号自动填入请假人所在部门的部门经理编号。

CREATER tIGGER `staff`.`insert_ask_leave` BEFORE INSERT ` staff`.`ask_leave` fOR EACH ROW BEGINC LARE manangeID1 INT;E CT `manageId` INTO manangeID1 FROM `dept` WHERE `id`=(LECT `deptId` FROM `employee` WHERE `id`=new.`empId`); new.`auditId`=manangeID1;