SQL2005 Establish a trigger for student course selection, and the corresponding student cannot choos

Create a new trigger by going to Database->Your Database->Table-> Triggers->Right-Click->.

create trigger tgr_classes_inserton classes

for insert -- Insert trigger.

as--Define variables.

declare @id int, @name varchar(20), temp int;--Grades.

It's the updated data for women, and forget about it.

if(exists(select @id = id, @name = name,@temp =temp from inserted where gender=female));

beginset @temp = @temp +5;

update student set temp = @temp where id=@id and name=@name;

endgo

---1、

create trigger tr_insert

on Student Table.

for insert

asdeclare @class_id int,stu_id int

select top 1 @class id=course number from (select course number,count(*)as total from select class table group by course number) t1 order by desc

select @stu id=student number from inserted

insert into the selection table values(@stu id, @class id, 0).

go---2、

create trigger tr_delete

on Student Table.

for delete

asdeclare @stu_id int

select @stu id=student ID from deleted

if exists (select 1 from where student ID=@stu ID).

beginraiserror ('Refuse to delete the record with student number %d because the student has a course selection record',@stu_id)

rollback transaction

end *Request that all the information in the relevant table be deleted at the same time as the student information is deleted (because you don't know your table, you can add the deletion of the additional corresponding table below according to the @stu ID (student number) * go.)

if(object_id('tri_up_c','tr') is not null)

drop trigger tri_up_cgocreate trigger tri_up_con cfor insert

asif(exists(select * from(select count(sno) as num from ct ct join c c on t where >

raiserror('If the number of students exceeds the number of students, it is not possible to enroll in the',16,11);

Go you try it first, and then ask me.

I also think that this should not be judged in the program by using triggers.

When you want to insert a course selection, first check whether you want to take the prerequisite course, if there is one, the data will be inserted, and if not, it will be prompted.

If you use triggers, you need to insert the data first, then trigger the operation query, and if it doesn't work, you can roll back the data.

There are many operation steps, which wastes system resources.

However, if you must use a trigger, change the if condition of 1L to 1L.

if exists(

select * from stu cno a inner join inserted b on inner join Elective Schedule c on

where

This doesn't have to be written in the database, but in the program!!

1. Create a stored procedure p kh with input parameters and outputs, and return the course number (as output parameters) of the course taught by the specified teacher (as input parameters).

2. Create and execute the stored procedure p cj3 with input and output parameters, query the course name and grade (output parameters) of the course selected by the student with the specified student number (input parameters), and after calling the stored procedure, the course name of the elective course with the student number is "xx", and its grade is: xx.

3. Check p xsqk, p kh, p cj3 respectively.

4. Create a trigger named TR INSERT CJ on the XS KC table in the XSCJ library, and trigger the trigger when inserting to the XS KC table, and give the prompt message "A new result is inserted into the XS KC table!" ”。

5. Create a trigger named TR Delete XSQK on the xsqk table in the xscj library, when you want to delete the row with the specified student number, activate the trigger, undo the deletion operation, and give the prompt message "You can't delete the information in the XSQK table!" ”。

6. Create a trigger named TR update XS KC on the XS KC table, which is used to detect whether the "Grade" column of the table is updated, and when there is an update, the student ID, course ID, original grade and new grade will be displayed.

7. Delete the TR INSERT CJ trigger.

This is the first trigger to change the gradeChange table, create trigger [up] on the student takeover for update

asif update(grade)

begininsert into gradechangeselect * from insertedwhere grade between 0 and 100endreturn

You can change the rest according to your table.