Trigger confusion- - probably simple!

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)

Trigger confusion- - probably simple!

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-09-26 : 22:26:30

Hi all,
Here's my latest dilemma. I have a pretty simple trigger:



create trigger tr_update_requests on po_preq_lin
for insert,update,delete
as

update rl
set
rl.ps_tkt_lin_str_id=tl.str_id,
rl.ps_tkt_lin_sta_id=tl.sta_id,
rl.ps_tkt_lin_seq_no=tl.seq_no
from inserted i
inner join po_preq_lin rl
on i.ITEM_NO = rl.ITEM_NO
and i.SEQ_NO=rl.SEQ_NO
and i.PREQ_NO = rl.PREQ_NO
inner join ps_tkt_lin tl
on rl.COMMNT_1 = tl.tkt_no
and rl.ITEM_NO=tl.ITEM_NO

The problem is, if PS_TKT_LIN has two of the same items on the same ticket, then the trigger gets 'confused' and inserts the same sequence number into po_preq_lin.ps_tkt_lin_seq_no.
something like:


commnt_1   item_no  ps_tkt_li_seq_no
-------------------------------------
12345         abcd        1
1             abcd        1

When it should be:


commnt_1   item_no  ps_tkt_li_seq_no
-------------------------------------
12345       abcd        1
12345       abcd        2

We need to keep this field unique to each row from ps_tkt_lin.
I'm stuck. How can I make this trigger update whenever it needs to, but keep the sequence numbers unique to each line from ps_tkt_lin?

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-27 : 05:17:54

The trigger won't do anything for deletes so you should make it for insert, update only.
Also the trigger does an update not an insert so it can't be the thing that is inserting into this table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-09-27 : 06:29:55

Hey nr,
You're correct; my description and code are misleading. This trigger is indeed for update, and does not do the insert. The insert is done by an app, and i have no control over how it does the work. perhaps I am going about this wrong. When we create purchase requests, rows are inserted into po_preq_lin with ITEM_NO and COMMNT_1 matching. The above problem is corrct, though, as there is no way to uniquely identify multiple items with the same item number on the same ticket. Any ideas?
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-27 : 08:55:58

Sounds like there are just two tables involved.
Maybe you should post the relevant columns from those tables (including unique indexes) and say what you are trying to achieve.
Post what the application inserts and what the trigger is trying to do to the data.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-09-27 : 11:00:33

Ok nr,
and thanks for the help. I've posted the relevant columns for each table, plus I left ALL of the constraints in the code just in case:

CREATE TABLE [PO_PREQ_LIN] (
	[PREQ_NO] [T_DOC_NO] NOT NULL ,
	[SEQ_NO] [T_SEQ_NO] NOT NULL ,
	[ITEM_NO] [T_ITEM_NO] NOT NULL ,
	[VEND_NO] [T_VEND_NO] NOT NULL ,
	[VEND_ITEM_NO] [T_ITEM_NO] NULL ,
	[ORD_QTY] [T_QTY] NOT NULL ,
	[ORD_UNIT] [T_UNIT] NULL ,
	[ORD_QTY_UNIT] [T_FLG] NOT NULL CONSTRAINT [DF_PO_PREQ_LIN_ORD_QTY_UNIT] DEFAULT ('0'),
	[ORD_QTY_NUMER] [T_CONV_FAC] NOT NULL CONSTRAINT [DF_PO_PREQ_LIN_ORD_QTY_NUMER] DEFAULT (1),
	[ORD_QTY_DENOM] [T_CONV_FAC] NOT NULL CONSTRAINT [DF_PO_PREQ_LIN_ORD_QTY_DENOM] DEFAULT (1),
	[DELIV_DAT] [T_DAT] NULL ,
	[CANCEL_DAT] [T_DAT] NULL ,
	[ORD_COST] [T_COST] NOT NULL ,
	[ORD_EXT_COST] [T_MONEY] NOT NULL CONSTRAINT [DF_PO_PREQ_LIN_ORD_EXT_COST] DEFAULT (0),
	[COMMNT_1] [T_COMMNT] NULL ,
	[COMMNT_2] [T_COMMNT] NULL ,
	[COMMNT_3] [T_COMMNT] NULL ,
	[CELL_DESCR] [T_LONG_DESCR] NULL ,
	[IS_SINGLE_CELL] [T_BOOL] NOT NULL CONSTRAINT [DF_PO_PREQ_LIN_IS_SINGLE_CELL] DEFAULT ('N'),
	[PS_TKT_LIN_STR_ID] [T_COD] NULL ,
	[PS_TKT_LIN_STA_ID] [T_COD] NULL ,
	[PS_TKT_LIN_SEQ_NO] [int] NULL ,
	CONSTRAINT [PK_PO_PREQ_LIN] PRIMARY KEY  CLUSTERED 
	(
		[PREQ_NO],
		[SEQ_NO]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_PO_PREQ_LIN_PO_PREQ_HDR] FOREIGN KEY 
	(
		[PREQ_NO]
	) REFERENCES [PO_PREQ_HDR] (
		[PREQ_NO]
	),
	CONSTRAINT [CK_PO_PREQ_LIN_CANCEL_DAT] CHECK ([CANCEL_DAT] is null or [CANCEL_DAT] = [dbo].[fnDateOnly]([CANCEL_DAT])),
	CONSTRAINT [CK_PO_PREQ_LIN_DELIV_DAT] CHECK ([DELIV_DAT] is null or [DELIV_DAT] = [dbo].[fnDateOnly]([DELIV_DAT])),
	CONSTRAINT [CK_PO_PREQ_LIN_IS_SINGLE_CELL] CHECK ([IS_SINGLE_CELL] = 'N' or [IS_SINGLE_CELL] = 'Y'),
	CONSTRAINT [CK_PO_PREQ_LIN_ORD_COST] CHECK ([ORD_COST] >= 0),
	CONSTRAINT [CK_PO_PREQ_LIN_ORD_EXT_COST] CHECK ([ORD_EXT_COST] >= 0),
	CONSTRAINT [CK_PO_PREQ_LIN_ORD_QTY] CHECK ([ORD_QTY] >= 0),
	CONSTRAINT [CK_PO_PREQ_LIN_ORD_QTY_DENOM] CHECK ([ORD_QTY_DENOM] >= 1),
	CONSTRAINT [CK_PO_PREQ_LIN_ORD_QTY_NUMER] CHECK ([ORD_QTY_NUMER] >= 1),
	CONSTRAINT [CK_PO_PREQ_LIN_ORD_QTY_UNIT] CHECK ([ORD_QTY_UNIT] = '5' or ([ORD_QTY_UNIT] = '4' or ([ORD_QTY_UNIT] = '3' or ([ORD_QTY_UNIT] = '2' or ([ORD_QTY_UNIT] = '1' or [ORD_QTY_UNIT] = '0'))))),
	CONSTRAINT [CK_PO_PREQ_LIN_SEQ_NO] CHECK ([SEQ_NO] >= 0)
) ON [PRIMARY]
GO


CREATE TABLE [PS_TKT_LIN] (
	[STR_ID] [T_COD] NOT NULL ,
	[STA_ID] [T_COD] NOT NULL ,
	[TKT_NO] [T_DOC_NO] NOT NULL ,
	[SEQ_NO] [T_SEQ_NO] NOT NULL ,
	[LIN_TYP] [T_FLG] NOT NULL CONSTRAINT [DF_PS_TKT_LIN_LIN_TYP] DEFAULT ('S'),
	[ITEM_NO] [T_ITEM_NO] NOT NULL ,
	[QTY_SOLD] [T_QTY] NOT NULL CONSTRAINT [DF_PS_TKT_LIN_QTY_SOLD] DEFAULT (1),
	[QTY_NUMER] [T_CONV_FAC] NOT NULL CONSTRAINT [DF_PS_TKT_LIN_QTY_NUMER] DEFAULT (1),
	[QTY_DENOM] [T_CONV_FAC] NOT NULL CONSTRAINT [DF_PS_TKT_LIN_QTY_DENOM] DEFAULT (1),
	[QTY_UNIT] [T_UNIT] NULL ,
	[SELL_UNIT] [T_FLG] NOT NULL CONSTRAINT [DF_PS_TKT_LIN_SELL_UNIT] DEFAULT ('0'),
	[PRC] [T_PRC] NULL ,
	[ORDERING_INDEX] [T_INT] NULL ,
	[ITEM_ORDER_STATUS] [T_FLG] NULL ,
	[ITEM_ORDER_NO] [T_PO_NO] NULL ,
	[LST_ITEM_STAT_DT] [T_DAT] NULL ,
	CONSTRAINT [PK_PS_TKT_LIN] PRIMARY KEY  CLUSTERED 
	(
		[STR_ID],
		[STA_ID],
		[TKT_NO],
		[SEQ_NO]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_PS_TKT_LIN_PS_TKT_HDR] FOREIGN KEY 
	(
		[STR_ID],
		[STA_ID],
		[TKT_NO]
	) REFERENCES [PS_TKT_HDR] (
		[STR_ID],
		[STA_ID],
		[TKT_NO]
	),
	CONSTRAINT [CK_PS_TKT_LIN_AUTO_SELECT] CHECK ([AUTO_SELECT] = 'N' or ([AUTO_SELECT] = 'Y' or [AUTO_SELECT] = '!')),
	CONSTRAINT [CK_PS_TKT_LIN_HAS_PRC_OVRD] CHECK ([HAS_PRC_OVRD] = 'N' or [HAS_PRC_OVRD] = 'Y'),
	CONSTRAINT [CK_PS_TKT_LIN_INV_COMMITTED] CHECK ([INV_COMMITTED] = 'N' or [INV_COMMITTED] = 'Y'),
	CONSTRAINT [CK_PS_TKT_LIN_IS_FOOD_STMP_LIN] CHECK ([IS_FOOD_STMP_LIN] = 'N' or [IS_FOOD_STMP_LIN] = 'Y'),
	CONSTRAINT [CK_PS_TKT_LIN_IS_SINGLE_CELL] CHECK ([IS_SINGLE_CELL] = 'N' or [IS_SINGLE_CELL] = 'Y'),
	CONSTRAINT [CK_PS_TKT_LIN_IS_TXBL] CHECK ([IS_TXBL] = 'N' or [IS_TXBL] = 'Y'),
	CONSTRAINT [CK_PS_TKT_LIN_ITEM_TYP] CHECK ([ITEM_TYP] = 'D' or ([ITEM_TYP] = 'S' or ([ITEM_TYP] = 'N' or [ITEM_TYP] = 'I'))),
	CONSTRAINT [CK_PS_TKT_LIN_LIN_TYP] CHECK ([LIN_TYP] = 'G' or ([LIN_TYP] = 'F' or ([LIN_TYP] = 'U' or ([LIN_TYP] = 'L' or ([LIN_TYP] = 'P' or ([LIN_TYP] = 'B' or ([LIN_TYP] = 'O' or ([LIN_TYP] = 'R' or [LIN_TYP] = 'S')))))))),
	CONSTRAINT [CK_PS_TKT_LIN_LOY_PGM_RDM_ELIG] CHECK ([LOY_PGM_RDM_ELIG] = 'N' or [LOY_PGM_RDM_ELIG] = 'Y'),
	CONSTRAINT [CK_PS_TKT_LIN_NORM_IS_TXBL] CHECK ([NORM_IS_TXBL] = 'N' or [NORM_IS_TXBL] = 'Y'),
	CONSTRAINT [CK_PS_TKT_LIN_PRC] CHECK ([PRC] is null or [PRC] <= 999999),
	CONSTRAINT [CK_PS_TKT_LIN_PRC_DECS] CHECK ([PRC_DECS] is null or [PRC_DECS] >= 0 and [PRC_DECS] <= 4),
	CONSTRAINT [CK_PS_TKT_LIN_PRC_GRP_TYP] CHECK ([PRC_GRP_TYP] = 'S' or ([PRC_GRP_TYP] = 'P' or ([PRC_GRP_TYP] = 'C' or [PRC_GRP_TYP] = '!'))),
	CONSTRAINT [CK_PS_TKT_LIN_PROMPT_DAT_1] CHECK ([PROMPT_DAT_1] is null or [PROMPT_DAT_1] = [dbo].[fnDateOnly]([PROMPT_DAT_1])),
	CONSTRAINT [CK_PS_TKT_LIN_PROMPT_DAT_2] CHECK ([PROMPT_DAT_2] is null or [PROMPT_DAT_2] = [dbo].[fnDateOnly]([PROMPT_DAT_2])),
	CONSTRAINT [CK_PS_TKT_LIN_PROMPT_DAT_3] CHECK ([PROMPT_DAT_3] is null or [PROMPT_DAT_3] = [dbo].[fnDateOnly]([PROMPT_DAT_3])),
	CONSTRAINT [CK_PS_TKT_LIN_PROMPTED_COST] CHECK ([PROMPTED_COST] = 'N' or [PROMPTED_COST] = 'Y'),
	CONSTRAINT [CK_PS_TKT_LIN_PROMPTED_DESCR] CHECK ([PROMPTED_DESCR] = 'N' or [PROMPTED_DESCR] = 'Y'),
	CONSTRAINT [CK_PS_TKT_LIN_PROMPTED_PRC] CHECK ([PROMPTED_PRC] = 'N' or [PROMPTED_PRC] = 'Y'),
	CONSTRAINT [CK_PS_TKT_LIN_PROMPTED_SER] CHECK ([PROMPTED_SER] = 'N' or [PROMPTED_SER] = 'Y'),
	CONSTRAINT [CK_PS_TKT_LIN_PROMPTED_UNIT] CHECK ([PROMPTED_UNIT] = 'N' or [PROMPTED_UNIT] = 'Y'),
	CONSTRAINT [CK_PS_TKT_LIN_QTY_DECS] CHECK ([QTY_DECS] is null or [QTY_DECS] >= 0 and [QTY_DECS] <= 4),
	CONSTRAINT [CK_PS_TKT_LIN_QTY_DENOM] CHECK ([QTY_DENOM] >= 1),
	CONSTRAINT [CK_PS_TKT_LIN_QTY_NUMER] CHECK ([QTY_NUMER] >= 1),
	CONSTRAINT [CK_PS_TKT_LIN_SELL_UNIT] CHECK ([SELL_UNIT] = '5' or ([SELL_UNIT] = '4' or ([SELL_UNIT] = '3' or ([SELL_UNIT] = '2' or ([SELL_UNIT] = '1' or [SELL_UNIT] = '0'))))),
	CONSTRAINT [CK_PS_TKT_LIN_SEQ_NO] CHECK ([SEQ_NO] >= 0),
	CONSTRAINT [CK_PS_TKT_LIN_TARE_WEIGHT] CHECK ([TARE_WEIGHT] is null or [TARE_WEIGHT] >= 0),
	CONSTRAINT [CK_PS_TKT_LIN_TRK_METH] CHECK ([TRK_METH] = 'S' or ([TRK_METH] = 'A' or ([TRK_METH] = 'G' or [TRK_METH] = 'N'))),
	CONSTRAINT [CK_PS_TKT_LIN_USE_COST_ENTD] CHECK ([USE_COST_ENTD] = 'N' or [USE_COST_ENTD] = 'Y')
) ON [PRIMARY]
GO

What happens is this:
When a user creates a purchase request, they are manually adding rows of data to po_preq_lin; The item number is added to ITEM_NO and the ticket number is added to field COMMNT_1. The problem is that there are instances where one ticket will have the same item number multiple times; say when a customer orders something and then calls later to add another of the same thing after the first one has been placed on a purchase request. So now we have two rows in ps_tkt_lin with the same TKT_NO and ITEM_NO, but with unique SEQ_NO's. PO_PREQ_LIN now has two rows with the same ITEM_NO and COMMNT_1, but no way to tell which row goes with which (so that ordering and recieving dates can be accurately tracked). What I am trying to accomplish is get the sequence numbers from PS_TKT_LIN into a field in PO_PREQ_LIN. Since the user is manually inputting rows into PO_PREQ_LIN, the app does nothing to connect the rows from each table. Perhaps a constraint will help here? We aren't talking about a lot of rows here; purchase requests get posted into purchase orders (different table) and deleted on a hourly basis, so we usually don't have more than 400-500 rows in PO_PREQ_LIN. If we can get it so that we loop through the PS_TKT_LIN table and move a SEQ_NO into PO_PREQ_LIN for each row and do nothing if the row already has a seq_no for it that would be great. Please keep in mind that PO_PREQ_LIN.SEQ_NO has no corrolation whatsoever with PS_TKT_LIN.SEQ_NO; each is used to uniquely identify the rows in each table per ticket/purchase request.
sorry for the long code!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-27 : 11:46:07

So what you want is to take the entries in inserted,
find the tickets that they correspond to and update them with the correct ticket sequence no.
The problem with your code is the join to ticket
something like this will update to the max ticket sequence no:


update rl
set	rl.ps_tkt_lin_str_id=tl.str_id,
	rl.ps_tkt_lin_sta_id=tl.sta_id,
	rl.ps_tkt_lin_seq_no=tl.seq_no
from inserted i
	join po_preq_lin rl
		on i.ITEM_NO = rl.ITEM_NO
		and i.SEQ_NO=rl.SEQ_NO
		and i.PREQ_NO = rl.PREQ_NO
	join ps_tkt_lin tl
		on rl.COMMNT_1 = tl.tkt_no
		and rl.ITEM_NO=tl.ITEM_NO
		and t1.seq_no = (	select max(t2.seq_no)
							from ps_tkt_lin t2
							where t2.tkt_no = t1.tkt_no
							and t2.ITEM_NO = t1.ITEM_NO
						)

This will update the entry to the latest ticket seq no which will be ok if they are inserted row by row - not for multiple row insertions.
Let me now if multiple rows are added to po_preq_lin in a single statement or multiple rows added to ps_tkt_lin followed by the corresponding rows to po_preq_lin (rather than single tkt + single preq)
It's possible to cater for those but is more complicated.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-09-27 : 12:13:32

Hey nr,
please excuse my ignorance; I THINK you are doing just what I need...
I came up with this procedure to test (Don't shoot me! it has a cursor! I promise to go to church as soon as we're done!):

  CREATE PROCEDURE [dbo].[pr_update_tkt_po_seq] AS
declare
@STR_ID		T_COD,
@STA_ID		T_COD,
@TKT_NO 	T_DOC_NO,
@ITEM_NO	T_ITEM_NO,
@SEQ_NO		int

begin
declare SEQ_CRSR cursor for (select t.str_id,t.sta_id,t.tkt_no,t.item_no,t.seq_no from ps_tkt_lin t
inner join po_preq_lin p on t.tkt_no=p.commnt_1 and t.item_no=p.item_no
group by t.tkt_no,t.item_no,t.seq_no,t.str_id,t.sta_id)
    open SEQ_CRSR
    fetch next from SEQ_CRSR 
      into @STR_ID,@STA_ID,@TKT_NO, @ITEM_NO, @SEQ_NO

    while @@FETCH_STATUS = 0
    begin

begin
set rowcount 1
update PO_PREQ_LIN
set PS_TKT_LIN_SEQ_NO=@SEQ_NO,
PS_TKT_LIN_STR_ID=@STR_ID,
PS_TKT_LIN_STA_ID=@STA_ID
where COMMNT_1=@TKT_NO and ITEM_NO=@ITEM_NO
and PS_TKT_LIN_SEQ_NO is null
end    
-- ---------------------------------------------------------------------------------

      fetch next from SEQ_CRSR 
        into @STR_ID,@STA_ID,@TKT_NO, @ITEM_NO, @SEQ_NO
    end
    close SEQ_CRSR
    deallocate SEQ_CRSR
  end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

This is working; is this what your muchbetter written code is doing?

quote:
Let me now if multiple rows are added to po_preq_lin in a single statement or multiple rows added to ps_tkt_lin followed by the corresponding rows to po_preq_lin (rather than single tkt + single preq)
It's possible to cater for those but is more complicated.

Yes, it is possible for multiple inserts (the right way to go about this); the app handles multiple inserts with an SP...
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-09-27 : 12:40:06

Looks reasonable but I think you need to exclude the sequence nos that are already in preq.
i.e.
add a ticket - will update preq correctly
add another ticket
will get both ticket rows in the cursor
will update one of them then update nothing with the second.

maybe if your cursor was

select t.str_id,t.sta_id,t.tkt_no,t.item_no,t.seq_no
from ps_tkt_lin t
inner join (select distinct commnt_1, item_no from inserted) i
on t.tkt_no=i.commnt_1
and t.item_no=i.item_no
left join po_preq_lin p
on t.tkt_no=p.commnt_1
and t.item_no=p.item_no
and t.seq_no = p.PS_TKT_LIN_SEQ_NO
where p.item_no is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-09-27 : 13:21:34

Hey nr,
Right on the money. Much thanks for your help.
I put your select script into the cursor stuff I wrote and
put it directly into a trigger rather than a procedure. It runs beautifully, and multiple inserts seem to work fine as well. Time now to do some testing and analysis to see if it needs any tweaking.

Now go have one of those
warm flat beers of yours and
watch the northern lights!

Thanks!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources