473,725 Members | 1,807 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 8762
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_in ters
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
14251
by: Hank | last post by:
I have two SQL Server 2000 machines (server_A and server_B). I've used sp_addlinkedserver to link them both, the link seems to behave fine. I can execute remote queries and do all types of neat things from one while logged onto the other. I'm working on a project to keep the data in the two systems synchronized, so I'm using triggers on both sides to update each other. For testing, I've created a simple, one-column table on both...
11
6631
by: raulgz | last post by:
I need audit triggers that change columns value in the same record that fire trigger. I need how to do.. Thanks..
2
3413
by: ecastillo | last post by:
i'm in a bit of a bind at work. if anyone could help, i'd greatly appreciate it. i have a web app connecting to a sql server using sql server authentication. let's say, for example, my login/password is dbUser/dbUser. the web app however, is using windows authentication. so if I am logged into the network as 'DOMAIN\Eric', when I access my web app, my web app knows that I am 'DOMAIN\Eric'. but to the sql server db, I am user...
4
1956
by: Dan | last post by:
I am loading data from table A into table B. Certain columns in B have check constraints. I'd like for any rows from A, which violate constraints, to be inserted into a third table, C. When the process is finished, I'll have only good rows in B, and exeption rows in C. I am investigating INSTEAD OF triggers, however my question to the group is, is there a better or best practice for this scenario? This must be common. Any high-level tips...
4
2716
by: stacdab | last post by:
We have a partitioned view with 4 underlying tables. The view and each of the underlying tables are in seperate databases on the same server. Inserts and deletes on the view work fine. We then add insert and delete triggers to each of the underlying tables. The triggers modify a different set of tables in the same database as the view (different than the underlying table). The problem is those triggers aren't fired when inserting or...
1
1977
by: Jeff Magouirk | last post by:
Dear Group, I would like to create an audit table that is created with a trigger that reflects all the changes(insert, update and delete) that occur in table. Say I have a table with Subject_ID, visit_number, dob, weight, height, User_name, inputdate The audit table would have .
0
2977
by: r0cboff | last post by:
Hi there, This is my first time posting in here and I'm hoping somebody can point out where I am going wrong? I am currently trying to use Bitemporal tables. By this I mean a table with a valid times and transaction times. These topics are covered by Joe Celko and Richard Snodgrass in their respective books. I have developed a simple schema to test the relevant constraints which are required to keep all the valid times and transaction...
0
4493
debasisdas
by: debasisdas | last post by:
This thread contains some useful tips/sample codes regarding TRIGGERS in oracle, that the forum members may find useful. TRIGGERS: =============== Database trigger is a PL/SQL block that is executed on an event in the database. The event is related to a particular data manipulation of a table such as inserting, deleting or updating a row of a table. Triggers may be used : 1.To implement complex business rule, which cannot be...
0
2413
by: petwir | last post by:
We've got a cascading delete being used in DB2. I've built new triggers under the parent/child tables in this cascading delete relationship. So when they delete the parent, and the cascade activity occurs, I had expected that the delete trigger on the child would always fire before the delete trigger on the parent. But I was wrong! They are firing parent-first child-second! This is causing me some concern because the sequence of the...
0
8751
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9401
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9257
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9171
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
4514
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4781
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3218
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2632
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2155
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.