Add an informational message to this trigger? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Add an informational message to this trigger?

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-01-14 : 21:41:30

Here's one I'm not sure about:
This trigger works ok, but it would be nice if it could raise a message without rolling back the transaction if the SVC_ZIP_ZONES table did not have a match for the PS_TKT_HDR.SHIP_ZIP_COD, something like 'The ship zip code does have sevice zone match. Please update the Service zipcode zone table.'
Can this be done?
I'm looking to return the message when this query

select zone from zip_cod_zones where zip_cod_zones.zip_cod=@ZIP

doesn't return anything in the code below.

CREATE TRIGGER enforcezonecharges ON dbo.PS_TKT_LIN
FOR INSERT, UPDATE
AS
set nocount on
/*declare local variables--*/
declare
@ZON T_FLG2,
@ZIP T_ZIP_COD,
@SVCFLG T_FLG2,
@Msg T_ERR_REF

select
@ZON=(select svc_zone from im_item where im_item.item_no=i.item_no),
@ZIP=(select ship_zip_cod
from ps_tkt_hdr
where ps_tkt_hdr.tkt_no=i.tkt_no
and ps_tkt_hdr.str_id=i.str_id
and ps_tkt_hdr.sta_id=i.sta_id),
@SVCFLG=(select IS_SVC_CALL
from ps_tkt_hdr
where ps_tkt_hdr.tkt_no=i.tkt_no
and ps_tkt_hdr.str_id=i.str_id
and ps_tkt_hdr.sta_id=i.sta_id)
from inserted i
where i.subcat_cod='LABOR'

/*make sure zone charge is correct if service related */
if @SVCFLG='Y' and @ZON>'00' and @ZON<>(select zone from zip_cod_zones where zip_cod_zones.zip_cod=@ZIP)
begin
rollback
raiserror('Zone charge on ticket does not match Ship to zone!',18,1)
end

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-14 : 22:47:46

If you don't want it to rollback, why not just remove the ROLLBACK statement and leave the raiserror?

I see a problem with the trigger in that it does not appear to be able to handle more that one row in the inserted table. When you write a trigger, it has to be able to handle more than one row being inserted or updated. If you use local variables, it would only be able to handle a single row in the inserted table.

The code below is closer to what you need to be able to handle multiple rows in the result set. However, I think this type of logic is best handled in the stored procedure that inserts the row into the table, instead of a trigger.


CREATE TRIGGER enforcezonecharges
ON
	dbo.PS_TKT_LIN
FOR
	INSERT, UPDATE
AS

set nocount on

if exists (
select
	*
from
	inserted i
	join
	ps_tkt_hdr
	on	ps_tkt_hdr.tkt_no = i.tkt_no	and
		ps_tkt_hdr.str_id = i.str_id	and
		ps_tkt_hdr.sta_id = i.sta_id
	join
	im_item
	on	im_item.item_no = i.item_no
where
	i.subcat_cod = 'LABOR'		and
	ps_tkt_hdr.IS_SVC_CALL = 'Y'		and
	im_item.svc_zone > '00'		and
	im_item.svc_zone not in 
		(
		select
			zip_cod_zones.zone
		from
			zip_cod_zones
		where
			zip_cod_zones.zone is not null and
			zip_cod_zones.zip_cod =
			ps_tkt_hdr.ship_zip_cod
		)
)
	begin
	raiserror('Zone charge on ticket does not match Ship to zone!',18,1)
	rollback
	end

CODO ERGO SUM

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-01-15 : 09:40:32

'Morning Michael,
I agree that the stored procedure should do this, but unfortunately I cannot change it (It is vendor supplied for the app). I see what you mean about multiple lines; I will use your code instead. However, what I was looking to do is have the trigger roll back the trans when the zone charge isn't correct for the zip code, and raise a separate information-only message if ps_tkt_hdr.ship_zip_cod doesn't exist in zip_code_zones.zip

I just took another look at your trigger, and I'm not sure about something. When we type a service ticket (PS_TKT_HDR.IS_SVC_CALL='Y') there may very well be more than one line on it, so there may be more than one row inserted/updated in PS_TKT_LIN. We need to be concerned with the line(s) that are of category 'LABOR'. The IM_ITEM.SVC_ZONE must =zip_code_zones.zone where zip_code_zones.ZIP_COD =PS_TKT_HDR.SHIP_ZIP_COD OR be ='00'. Is your trigger doing just that? I should just need to remove the

where zip_cod_zones.zone is not null

to have the same function (only MVJ improved) as my current trigger, right?
Andy

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

1/16/05
Ok, I'm a doofus. I re-read it again, and of course it is right...
I really need to slow down a bit!
As for the second message, I am looking to allow the transaction to finish, but let the user know that the appropriate zip code/zone id doesn't exist. Can raiserror be used for information only?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources