October 25, 2002 at 1:20 pm
Hello,
This is my first time working with the 'INSTEAD OF DELETE' trigger, and I am going to try my best to make this understandable:
I am trying to delete specific test scores from a test score table called 'MMtblStudentTestScoresTest'(which is actually a copy of the actual table the scores reside in) where there is a certain teacher record in another table called 'Teacher_Data_Main'. The individual test scores in the table 'MMtblStudentTestScoresTest' are tracked by a student id number in a field called 'PERMNUM' that originate from a student table called 'Student_Data_Main'.
FYI, all of the tables reside in SQL Server 2000 Standard. The teachers populate 'MMtblStudentTestScoresTest' through a form interface designed in an MS Access 2000 Project (not an MS Access datbase).
I can create a query that can identify a specific test that has been given a specific score for a specific student by a specific teacher as follows:
*********************************
select
sc.permnum as testpermnum,
sc.testshortname,
sc.testscore,
sc.dateEntered
from
student_data_main sd inner join
tblMMstudentTestScoresTest sc on sd.permnum=sc.permnum inner join
teacher_data_main tt on sd.teacherid=tt.teacherid
where
tt.systemusername = 'domain\username'
and sc.testshortname = 'the id for the test'
**********************************
Once I have identified the records I want, I need to delete them in some way other than using a delete statement for each individual record. I have tried to do this by using an INSTEAD OF DELETE trigger on a view called 'MMStudentTestsByTeacher_vw'.
The code for the view looks like this:
**********************************
CREATE View MMStudentTestsByTeacher_vw
as
select
sd.schoolnum,
sd.permnum,
sd.firstname,
sd.lastname,
sc.permnum as testpermnum,
sc.testshortname,
sc.testscore,
tt.firstname as teachFirst,
tt.lastname as teachLast,
tt.systemusername
from student_data_main sd inner join tblMMstudentTestScoresTEST sc on sd.permnum=sc.permnum
inner join teacher_data_main tt on sd.teacherid=tt.teacherid
******************************
The code for the trigger looks like this:
******************************
Create Trigger TR_DeleteSpecificTestRecords on MMStudentTestsByTeacher_vw
Instead of Delete
as
Delete tblMMStudentTestScoresTEST
from
tblMMstudentTestScoresTEST as sc
inner join student_data_main as sd on sc.permnum=sd.permnum
inner join teacher_data_main as tt on sd.teacherid=tt.teacherid
Having set up the View and the Trigger, when I try to run code like this:
*******************************
delete MMStudentTestsByTeacher_vw
where systemusername = 'domain\username'
and testshortname = 'the id for the test'
*******************************
I get confirmation that x number of records where deleted, and the number confirmed is correct. However, when I run a select statment for all records in 'tblMMStudentTestScoresTEST', I find that all records in the table have been deleted.
My guess is that there is something wrong with the trigger or in the way I am expressing the 'delete' code. Is there something else that could be happening?
If you have any ideas on this, please let me know. I hope I have provided enough information. Thanks for your help.
CSDunn
October 26, 2002 at 4:59 am
I did not read through all of your table definition stuff, so I might be missing something. But I don't see a WHERE clause in the delete statement inside your trigger, so I guess everything gets deleted.
Inside a 'DELETE' trigger, you have a 'deleted' table that contains all rows that would normally be deleted. So you should only delete records from the table that are also in the 'deleted' table. It's easy to do this using an 'EXISTS' clause or adding a join. There just has to be a way of uniquely identifying the record you want to delete.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply