Reset field on all rows if one changes

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 2008 Forums

Transact-SQL (2008)

Reset field on all rows if one changes

Author

Topic

Pilbum
Starting Member

1 Post

Posted - 2012-07-12 : 08:52:05

Hello,

I'm trying to create a trigger that will clear the "checked" field when all items with the same classification have been checked.

The purpose of this is to do cycle counting of inventory, which is when you count the stock levels of a proportion of stock that have the same classification.

The checked field will be used to record the date it was lasted checked and will cleared once all stock have counted, putting back into the pool of stock to be counted.

Therefore, the trigger is suppose to count the number of rows that share the same classification as the amended row, and compare that with the number of rows in that classification, that have a date in the checked date field. When the two values are the same, reset all the checked dates to blank.

But when I tested it, nothing happened :( Does anyone know why?

ALTER TRIGGER [dbo].[UDEF_Last_Checked_Reset] ON [dbo].[ASC_PMA_TBL]
AFTER INSERT
AS

BEGIN

DECLARE @PartOnly varchar(16) -- Part Number
DECLARE @Rev varchar(4) -- Part Revision
DECLARE @PartCode as varchar(2) -- Part Classification
DECLARE @Last_Checked as datetime -- Checked Date

SELECT @PartOnly = PMA_PART_ONLY,
@Rev = PMA_PART_REV,
@PartCode = PMA_PART_CODE,
@Last_Checked = [PMA_LAST_CHECK_DATE]

FROM Inserted

-----------------------------------------------------------------------------
declare @countpartcode as decimal(18,5) --Counter for part classification
declare @countlastcheck as decimal(18,5) -- Counter for completed parts
--set counters
set @countpartcode = (SELECT SUM(case when [PMA_PART_CODE]=@PartCode THEN 1 ELSE 0 END)from ASC_PMA_TBL)
set @countlastcheck = (select SUM(case when [PMA_LAST_CHECK_DATE] IS NULL AND PMA_PART_CODE = @PartCode THEN 1 ELSE 0 END) from asc_pma_tbl)
--compare counters
if @countpartcode = @countlastcheck
-- clear checked date field on all parts with same classifcation
UPDATE [ASC_PMA_TBL]
SET [PMA_LAST_CHECK_DATE] = ''
WHERE [PMA_PART_CODE] = @PartCode

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 10:04:10

your trigger is assuming only one row change at a time. Please keep in mind that for batch inserts trigger will be called once where inserted will have entire inserted records. So you might want to use table variable to get values from inserted table rather than variables.

Second thing is reg. your requirement. can you elaborate on how and when stock checking works? Does this have to be triggered by an insert?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-07-12 : 10:12:39

I already prepared this by the time Visakh responded but I'll post anyway...

couple problems:
1. you are setting check column to an empty space but checking for NULL.
2. you are only handling inserts - not updates to the table
3. you are only dealing with one row - what if more than one row is inserted or updated?

Perhaps this:


ALTER TRIGGER [dbo].[UDEF_Last_Checked_Reset] ON [dbo].[ASC_PMA_TBL]
AFTER INSERT, update
AS
BEGIN
	update a set
		a.[PMA_LAST_CHECK_DATE] = NULL
	From	ASC_PMA_TBL a
	join	(
		select i.pma_part_code
		from	inserted i
		join	ASC_PMA_TBL a on a.pma_part_code = i.pma_part_code
		group by i.pma_part_code
		having count(*) = sum(case when isNull([PMA_LAST_CHECK_DATE],'') = '' then 1 else 0 end)
		) d on d.ASC_PMA_TBL = a.ASC_PMA_TBL
end
go

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources