mis.ddl

这是一个自己研发的薪酬管理系统

go

alter table "wh_tech_bonuses"
	add constraint "wh_projects_wh_tech_bonuses_FK1" foreign key (
		"project_id")
	 references "wh_projects" (
		"project_id") on update no action on delete no action  

go

/* 在表 "wh_salaries" 中添加外键约束。                                                                  */
alter table "wh_salaries"
	add constraint "wh_employees_wh_salaries_FK1" foreign key (
		"employee_id")
	 references "wh_employees" (
		"employee_id") on update no action on delete no action  

go

/* 在表 "wh_market_bonuses" 中添加外键约束。                                                            */
alter table "wh_market_bonuses"
	add constraint "wh_employees_wh_market_bonuses_FK1" foreign key (
		"employee_id")
	 references "wh_employees" (
		"employee_id") on update no action on delete no action  

go

alter table "wh_market_bonuses"
	add constraint "wh_projects_wh_market_bonuses_FK1" foreign key (
		"project_id")
	 references "wh_projects" (
		"project_id") on update no action on delete no action  

go

/* 在表 "wh_employees" 中添加外键约束。                                                                 */
alter table "wh_employees"
	add constraint "wh_departments_wh_employees_FK1" foreign key (
		"dpt_id")
	 references "wh_departments" (
		"dpt_id") on update no action on delete no action  

go

/* 创建新视图。                                                                                     */
/* 创建视图 view_market_bonus。                                                                    */

CREATE VIEW dbo.view_market_bonus
AS
SELECT dbo.wh_employees.employee_name, dbo.wh_projects.project_name, 
      dbo.wh_projects.project_begin, dbo.wh_projects.project_end, 
      dbo.wh_projects.project_finish, 
      dbo.wh_market_bonuses.mbonus_sum * dbo.wh_market_bonuses.mbonus_quota * dbo.wh_market_bonuses.mbonus_percent
       / 10000 AS mbonus_prize, dbo.wh_market_bonuses.mbonus_isgive, 
      dbo.wh_market_bonuses.mbonus_date, dbo.wh_employees.employee_id, 
      dbo.wh_employees.dpt_id, dbo.wh_market_bonuses.mbonus_id, 
      dbo.wh_projects.project_id
FROM dbo.wh_employees INNER JOIN
      dbo.wh_market_bonuses ON 
      dbo.wh_employees.employee_id = dbo.wh_market_bonuses.employee_id INNER JOIN
      dbo.wh_projects ON dbo.wh_market_bonuses.project_id = dbo.wh_projects.project_id




go

/* 创建视图 view_market_bonus2。                                                                   */

CREATE VIEW dbo.view_market_bonus2
AS
SELECT employee_name, project_name, mbonus_prize, mbonus_isgive, mbonus_date, 
      employee_id, dpt_id, mbonus_id, project_id
FROM dbo.view_market_bonus
WHERE (mbonus_isgive = 0)




go

/* 创建视图 view_projects。                                                                        */

CREATE VIEW dbo.view_projects
AS
SELECT project_id, project_name, 
      project_firstpay - project_cost - project_operation - project_codes - project_host - project_dns
       - project_search - project_mail - project_other AS project_gain, 
      project_lastpay - project_cost2 - project_operation2 - project_codes2 - project_host2 - project_dns2
       - project_search2 - project_mail2 - project_other2 AS project_gain2, project_notpay, 
      project_notpay / project_sum AS project_not
FROM dbo.wh_projects




go

/* 创建视图 view_salaries。                                                                        */

CREATE VIEW dbo.view_salaries
AS
SELECT dbo.wh_employees.employee_name, dbo.wh_salaries.salary_month, 
      dbo.wh_salaries.salary_date, dbo.wh_salaries.salary_basic, 
      dbo.wh_salaries.salary_tech, dbo.wh_salaries.salary_eduage, 
      dbo.wh_salaries.salary_duty, dbo.wh_salaries.salary_prize1, 
      dbo.wh_salaries.salary_prize2, dbo.wh_salaries.salary_manage, 
      dbo.wh_salaries.salary_special, dbo.wh_salaries.salary_food, 
      dbo.wh_salaries.salary_basic + dbo.wh_salaries.salary_tech + dbo.wh_salaries.salary_eduage
       + dbo.wh_salaries.salary_duty + dbo.wh_salaries.salary_prize1 + dbo.wh_salaries.salary_prize2
       + dbo.wh_salaries.salary_manage + dbo.wh_salaries.salary_special + dbo.wh_salaries.salary_food
       AS salary_xiaoji, dbo.wh_salaries.salary_hols, dbo.wh_salaries.salary_break, 
      dbo.wh_salaries.salary_insurance, dbo.wh_salaries.salary_tax, 
      dbo.wh_salaries.salary_late, dbo.wh_salaries.salary_other, 
      dbo.wh_salaries.salary_basic + dbo.wh_salaries.salary_tech + dbo.wh_salaries.salary_eduage
       + dbo.wh_salaries.salary_duty + dbo.wh_salaries.salary_prize1 + dbo.wh_salaries.salary_prize2
       + dbo.wh_salaries.salary_manage + dbo.wh_salaries.salary_special + dbo.wh_salaries.salary_food
       - dbo.wh_salaries.salary_hols - dbo.wh_salaries.salary_break - dbo.wh_salaries.salary_insurance
       - dbo.wh_salaries.salary_tax - dbo.wh_salaries.salary_late - dbo.wh_salaries.salary_other
       AS salary_total, dbo.wh_employees.employee_id
FROM dbo.wh_employees INNER JOIN
      dbo.wh_salaries ON dbo.wh_employees.employee_id = dbo.wh_salaries.employee_id




go

/* 创建视图 view_tech_bonus。                                                                      */

CREATE VIEW dbo.view_tech_bonus
AS
SELECT dbo.wh_employees.employee_name, dbo.wh_projects.project_name, 
      dbo.wh_projects.project_begin, dbo.wh_projects.project_end, 
      dbo.wh_projects.project_finish, 
      dbo.wh_tech_bonuses.tbonus_firstsum * dbo.wh_tech_bonuses.tbonus_quote * dbo.wh_tech_bonuses.tbonus_fpercent
       / 10000 AS tbonus_fprize, dbo.wh_tech_bonuses.tbonus_fisgive, 
      dbo.wh_tech_bonuses.tbonus_fdate, 
      dbo.wh_tech_bonuses.tbonus_lastsum * dbo.wh_tech_bonuses.tbonus_quote * dbo.wh_tech_bonuses.tbonus_lpercent
       / 10000 AS tbonus_lprize, dbo.wh_tech_bonuses.tbonus_lisgive, 
      dbo.wh_tech_bonuses.tbonus_ldate, dbo.wh_employees.employee_id, 
      dbo.wh_employees.dpt_id, dbo.wh_projects.project_id, 
      dbo.wh_tech_bonuses.tbonus_id
FROM dbo.wh_employees INNER JOIN
      dbo.wh_tech_bonuses ON 
      dbo.wh_employees.employee_id = dbo.wh_tech_bonuses.employee_id INNER JOIN
      dbo.wh_projects ON dbo.wh_tech_bonuses.project_id = dbo.wh_projects.project_id




go

/* 创建视图 view_tech_bonus2。                                                                     */

CREATE VIEW dbo.view_tech_bonus2
AS
SELECT project_name, employee_name, tbonus_fprize, tbonus_fisgive, tbonus_fdate, 
      tbonus_lprize, tbonus_lisgive, tbonus_ldate, employee_id, project_id, 
      tbonus_id
FROM dbo.view_tech_bonus
WHERE (tbonus_lisgive = 0) AND (tbonus_lprize > 0) OR
      (tbonus_fisgive = 0)




go

/* 为所有新创建和发生更改的表创建/重新创建用户定义的触发器。                                                              */

/* 为表 wh_employees 创建表级触发器。                                                                   */

CREATE TRIGGER trig_delete_emp on wh_employees

INSTEAD OF delete

AS

BEGIN

  delete wh_salaries where employee_id in(select employee_id from deleted)

  delete wh_tech_bonuses where employee_id in(select employee_id from deleted)

  delete wh_market_bonuses where employee_id in(select employee_id from deleted)

  delete wh_employees where employee_id in(select employee_id from deleted)

END





go

/* 为表 wh_projects 创建表级触发器。                                                                    */


--2.删除一个项目，也将奖金里相关的信息删除

create trigger trig_delete_project on wh_projects

INSTEAD OF delete

as

delete wh_tech_bonuses where project_id in(select project_id from deleted)

delete wh_market_bonuses where project_id in(select project_id from deleted)

delete wh_projects where project_id in(select project_id from deleted)





go


/* 这是 Microsoft Visual Studio 生成的 SQL DDL 脚本的末尾。                                              */