Triggers on Delete and on Insert & SQL Server 2000

[Shock]

Hi everybody,

I just wrote my first two triggers and from the minimal amount of testing I
have done, they work! However, I was hoping I could get some feedback from
those of you more experienced in writing triggers.

Here is the first one:
CREATE TRIGGER DecreInters ON Interaction
FOR DELETE
AS
declare @stu INT
declare @num INT

select @stu = Student_FK from deleted
select @num = (select Inters from Student where Student_Key = @stu)

UPDATE Student
SET Inters = @num - 1
FROM Student
WHERE Student.Student_Key = @stu

Here is the second one:
CREATE TRIGGER IncreInters
ON Interaction
AFTER INSERT
AS

declare @stu INT
declare @num INT
declare @last_rec INT

select @last_rec = @@IDENTITY
select @stu = (select Student_FK from Interaction where Interaction_ID =
@last_rec)
select @num = (select Inters from Student where Student_Key = @stu)

UPDATE Student
SET Inters = @num + 1
FROM Student
WHERE Student.Student_Key = @stu

Are there any shortcuts I could use or things I could do to make these
triggers more efficient. Please give me some feedback and let me know of
any problems that might possibly be caused due to my doing this improperly.

Thanks ahead,

Corey

[David Portas]

Your triggers will work if you delete/insert a single row but not for
multiple row delete/inserts. A trigger gets called once per
DELETE/INSERT/UPDATE statement so you _must_ allow for statements which
operate on multiple rows. The trigger might look something like this:

CREATE TRIGGER trg_Interaction ON Interaction
FOR INSERT, DELETE
AS
UPDATE Student
SET inters = inters +
(SELECT COUNT(*)
FROM Inserted
WHERE student_fk = Student.student_key)
-
(SELECT COUNT(*)
FROM Deleted
WHERE student_fk = Student.student_key)
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE student_fk = Student.student_key
UNION ALL
SELECT *
FROM Deleted
WHERE student_fk = Student.student_key)

However, the most efficient solution might be to avoid a trigger altogether
and use a view instead:

CREATE VIEW Student_with_inters
AS
SELECT S.student_key, I.inters, ... other columns
FROM Student AS S
LEFT JOIN
(SELECT student_fk,
COUNT(*) AS inters
FROM Interaction
GROUP BY student_fk) AS I
ON S.student_key = I.student_fk

--
David Portas
------------
Please reply only to the newsgroup
--