genxin.sql

一个基于SQL架构的管理系统

insert into student_info(student_ID)
 values (2) 

insert into student_info(student_ID)
 values (3)

insert into student_info(student_ID)
 values (4)

----------------------学生添加记录后自动更新登陆用户  ''''密码和权限问题
CREATE TRIGGER AddUser
 ON [dbo].[student_Info] 
 FOR INSERT
 AS
 insert into user_info(user_ID,user_PWD,user_Des)
  select student_ID,student_ID,'student' from student_Info 
  where student_ID not in(select user_ID from user_info)
问题：用户密码初始为空
-------------------------删除一个学生记录后自动删除登陆用户

create trigger ttr3
on student_Info
for delete
as 
delete 
from user_info
where user_ID  like '1%'
 and user_ID not in(select student_ID from student_Info)


'''''学生的学号必须以1开头



--------------------------------------------学生毕业后自动删除记录
create trigger deleteJILU
on student_Info
for insert,update
as 
delete 
from student_Info
where student_ID in(select student_ID from student_Info where stu_Exit<getdate())

----------------------------------------自动设置毕业时间
create trigger EXIT_Date
on student_Info
for insert,update
as 
update student_Info
set stu_Exit=dateadd(year,stu_Year,entr_Date)
-----------------------------------------------