October 10, 2005 at 2:25 pm
Dear List Board:
I have a trigger that should delete a row based on some column values. I'd say that 99.0% of time it works fine. The other 1% of the time, it does not delete the row.
Any suggestions? It's driving my crazy! It's pretty simple and just should delete the row if the qty.avail=0 and compont_qyt=0. From time to time I run the following sql and find that it does not delete all of the time.
select * from content where (avail_qty = 0 and component_qty = 0)
Here is the code for the trigger:
CREATE TRIGGER tr_content_update
ON CONTENT
FOR update
AS
/* */
/* delete content when qty.avail = zero and */
/* component_qty = zero */
/* */
/* Updated on December 18, 2002 */
/* Add Container_ID and Expiration_Date to the */
/* Where cause */
/* */
DECLARE @project_id char (10),
@SKU char (50),
@Supp_Code char (20),
@Owner_ID numeric(8,0),
@Country_of_Origin char (3),
@WH_Code char (10),
@L_Code char (10),
@Inventory_Type char (1),
@Lot_No char (20),
@Serial_No char (20),
@RO_No char (20),
@PO_No char (25),
@PO_No2 char (25),
@Container_ID char (25),
@Expiration_Date datetime,
@Avail_qty numeric(15,5),
@Component_Qty numeric(15,5)
Select @project_id = project_id,
@SKU = SKU,
@Supp_Code = Supp_Code,
@Owner_id = Owner_id,
@Country_of_Origin = Country_of_Origin,
@WH_Code = WH_Code,
@L_Code = L_Code,
@Inventory_Type = Inventory_Type,
@Serial_No = Serial_No,
@Lot_No = Lot_No,
@RO_No = RO_No,
@PO_No = PO_No,
@PO_No2 = PO_No2,
@Container_ID = Container_ID,
@Expiration_Date = Expiration_Date,
@Avail_Qty = Avail_Qty,
@Component_Qty = Component_Qty
FROM inserted
If (@avail_qty = 0 and @component_qty = 0)
BEGIN
DELETE from content
WHERE (( project_id = @Project_ID ) and
( SKU = @SKU ) and
( Supp_Code = @Supp_Code ) and
( Owner_id = @Owner_id ) and
( Country_of_Origin = @Country_of_Origin ) and
( WH_Code = @WH_Code ) and
( L_Code = @L_Code ) and
( Inventory_Type = @Inventory_Type ) and
( Lot_No = @Lot_No ) and
( Serial_No = @Serial_No ) and
( RO_No = @RO_No ) and
( PO_No = @PO_No ) and
( PO_No2 = @PO_No2 ) and
( Container_ID = @Container_ID ) and
( Expiration_Date = @Expiration_Date))
END
/* insert into content_history when new row inserted into Content */
Thanks
Bill Dillon
October 10, 2005 at 2:32 pm
Without your code, tables and data scenarios that do not work it will be difficult to help you.
its probably a data scenario that your code does not account for.
October 10, 2005 at 4:42 pm
Looking at your code this trigger will only work if one row at a time is updated.
In sql server a trigger is not fired once per row update but once per transaction therefore if more than one row is updated at a time your trigger will not work.
If you give us the table definition and other available info we could make a suggestion but I would guess when it doesn't work it's because more than one row has been updated but the trigger as it stands can't cope with that.
hth
David
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply