AFTER INSERT Trigger, multi-row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

AFTER INSERT Trigger, multi-row

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2009-02-17 : 12:48:41

I have a standard AFTER INSERT Trigger. It appears that the Inserted" table can contain multiple records. I need to perform an update to another table based on the values in the inserted record, however, the fact that multiple records are within the inserted table breaks my code. The following works fine when one record is being inserted, but breaks when multiple records are being inserted. Is there a way to modify this to accomplish what I'm looking for?



CREATE TRIGGER [t_insertClientAssignmentLog]
ON [dbo].[ClientAssignmentLog]
AFTER INSERT

AS

SET NOCOUNT ON

DECLARE @tempTable TABLE
(
    T_Employee_ID nchar(15) NOT NULL,
    T_Client_ID nchar(15) NOT NULL,
    T_EntryType tinyint,
    T_LoggedWhen datetime
)
-- Prepopulate with all record(s) being inserted

INSERT INTO @tempTable(T_Employee_ID, T_Client_ID, T_EntryType, T_LoggedWhen)
SELECT I.Employee_ID, I.Client_ID, I.EntryType, I.LoggedWhen
FROM inserted as I


DECLARE @entryType tinyint

SELECT @entryType=T_EntryType FROM @tempTable

IF @entryType=0

	BEGIN

		UPDATE dbo.[Clients]
		SET primaryemployee_id=NULL
		WHERE client_id = 
			(SELECT T_Client_ID FROM @tempTable)
			AND primaryemployee_id=
			(SELECT T_Employee_ID FROM @tempTable)
	END

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-17 : 13:09:39

First I would change your test condition and use exists:
if exists(select t_entryType from @temptable where t_entrype=0)

Second, in your update statement I would change your where clause to a join on the inserted table and then add a where clause for t_entry_type=0

update dbo.[clients]
set primaryemployee_id=null
From inserted i
inner join dbo.[clients] c
on i.t_client_id=c.client_id
and i.t_employee_id=c.primaryemployee_id
where i.t_entry_type=0

Mike
"oh, that monkey is going to pay"

akashenk
Posting Yak Master

111 Posts

Posted - 2009-02-17 : 13:44:23

Thanks! That seems to have done the trick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources