469,900 Members | 1,678 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,900 developers. It's quick & easy.

Triggers on Delete and on Insert & SQL Server 2000

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
Jul 20 '05 #1
1 8536
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
--
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by raulgz | last post: by
1 post views Thread by Jeff Magouirk | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.