Alter Trigger - SQL Server Forums

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)

Alter Trigger

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-21 : 09:02:58

I have a on update trigger I need to change to go only when someone updates the Case_Count field.

Right now it seems to trigger on any update and I am Getting alot of duplication.

ALTER TRIGGER PalletCount_Sub_Trigger2
ON dbo.PalletCount_Sub
FOR update
AS

INSERT INTO dbo.PalletCount_edit
(Pallet_ID, Job_Number_Sub_Link, Pallet_Date, Case_Count,case_countold, Pallet_Number, Inv_Updated, Pallet_Item_Count, Pallet_Day, Partial_Case, Printed,
Pal_Type,Whoedit)
SELECT deleted.Pallet_ID, deleted.Job_Number_Sub_Link, deleted.Pallet_Date, inserted.Case_Count, deleted.Case_Count,deleted.Pallet_Number, deleted.Inv_Updated, deleted.Pallet_Item_Count, deleted.Pallet_Day, deleted.Partial_Case, deleted.Printed,
deleted.Pal_Type,SUSER_sname(SUSER_SID())

FROM Deleted, inserted

Where (Deleted.Case_Count <> inserted.Case_Count )

Jim
Users <> Logic

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-10-21 : 09:21:38

Is it possible the updates are affecting more than one row at a time? If so, your join on Deleted.Case_Count <> Inserted.Case_Count) is going to multiply all combinations of values, giving you the duplication you are talking about.

If this is the case, you'll need to modify the WHERE clause to tie records together based on a primary key.

Raymond

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-21 : 09:41:25

No the access screen only alows for one record at a time.

Jim
Users <> Logic

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-10-21 : 10:06:25

Sorry then Jim, 'cos running a simpler version of what you've got there gives me no trouble at all.

What about declaring a couple of variables, selecting the CASE_COUNT from Insert and Deleted into them and then using an "IF @inserted_var <> @deleted_var" around your insert statement?

Raymond

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-10-21 : 10:24:41

I am Going to try this for a while and see what happens.

ALTER TRIGGER PalletCount_Sub_Trigger2
ON dbo.PalletCount_Sub
FOR update
AS
Declare @dcase Int,@Icase int
select @dcase = Deleted.Case_Count, @Icase = inserted.Case_Count
From Deleted,inserted

If @dcase <> @Icase
Begin
INSERT INTO dbo.PalletCount_edit
(Pallet_ID, Job_Number_Sub_Link, Pallet_Date, Case_Count,case_countold, Pallet_Number, Inv_Updated, Pallet_Item_Count, Pallet_Day, Partial_Case, Printed,
Pal_Type,Whoedit)
SELECT deleted.Pallet_ID, deleted.Job_Number_Sub_Link, deleted.Pallet_Date, inserted.Case_Count, deleted.Case_Count,deleted.Pallet_Number, deleted.Inv_Updated, deleted.Pallet_Item_Count, deleted.Pallet_Day, deleted.Partial_Case, deleted.Printed,
deleted.Pal_Type,SUSER_sname(SUSER_SID())

From deleted,inserted

Where (Deleted.Case_Count <> inserted.Case_Count)
end

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources