|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
May 8th, 2003, 06:25 PM
|
|||
|
|||
updating field using trigger in Ms sql2000
hi guys,
need help with triggers. i have 2 tbls. tblContact & tblMemo (1-m) resp.. tblContact [PK contId(int 4), Name, MemoCount(int 4)] tblMemo[pk memoId (int 4), {FK} contId(int 4), memo (varchar 100)] I need to Write a trigger to automatically update the MemoCount field in the tblContact whenever a memo from tblMemo is inserted, updated or deleted. Assuming that the memoCount field might not have the correct value so have to take that into account when writting the trigger. any help will be really appreciated. am new to sql server.. thanks mat..
|
|
#2
August 5th, 2003, 02:45 PM
|
|||
|
|||
|
Try this. Loosely tested.
Code:
CREATE TRIGGER UpdateMemoCount ON [dbo].[tblMemo] FOR INSERT, UPDATE, DELETE AS DECLARE @ContactID int DECLARE @MemoCount int IF EXISTS(SELECT 1 FROM deleted) SELECT @contactID = contactID FROM deleted ELSE SELECT @contactID = contactID FROM inserted IF Exists(SELECT 1 FROM tblMemo WHERE contactID = @contactid) SELECT @memoCount = COUNT(*) FROM tblMemo WHERE contactId = @contactid ELSE SELECT @memoCount = 0 UPDATE tblContact SET memoCount = @memoCount WHERE contactID = @contactid There must be better and faster ways of doing this, but you get the picture.
__________________
- Rogier Doekes
|
|
| Viewing: Dev Articles Community Forums > Databases > Microsoft SQL Server > updating field using trigger in Ms sql2000 |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
