create trigger tr_updateitemshipdate
on dbo.ps_tkt_lin
for insert,update
as
set nocount on
update tl
set tl.tkt_ship_dat=th.ship_dat,
tl.item_ship_dat=
case
when tl.tkt_ship_dat is not null and tl.item_ship_dat is null
then tl.tkt_ship_dat
when d.tkt_ship_dat=tl.item_ship_dat
then i.tkt_ship_dat
when d.tkt_ship_dat<>tl.item_ship_dat
then tl.tkt_ship_dat
end
from ps_tkt_hdr th
inner join ps_tkt_lin tl
on tl.str_id=th.str_id
and tl.sta_id=th.sta_id
and tl.tkt_no=th.tkt_no
inner join inserted i
on i.str_id=tl.str_id
and i.sta_id=tl.sta_id
and i.tkt_no=tl.tkt_no
inner join deleted d
on d.str_id=tl.str_id
and d.sta_id=tl.sta_id
and d.tkt_no=tl.tkt_no
set noucount off

IF DELETED.TKT_SHIP_DAT(from PS_TKT_LIN)=PS_TKT_LIN.ITEM_SHIP_DAT
then set PS_TKT_LIN.ITEM_SHIP_DAT=INSERTED.TKT_SHIP_DAT(the new value for PS_TKT_LIN.TKT_SHIP_DAT)
ELSE IF DELETED.TKT_SHIP_DAT(from PS_TKT_LIN)<>PS_TKT_LIN.ITEM_SHIP_DAT
then set PS_TKT_LIN.ITEM_SHIP_DAT=ITSELF(no change)

create trigger tr_UpdateItemShipDate
on dbo.PS_TKT_HDR
for insert, update
as

update tl
set tl.ITEM_SHIP_DAT = 
case
when th.ship_dat is null
then tl.item_ship_dat
when th.ship_dat is not null and tl.Item_ship_dat is null
then th.ship_dat
when th.ship_dat is not null and tl.item_ship_dat=d.ship_dat
then th.ship_dat
when th.ship_dat is not null and tl.item_ship_dat<>d.ship_dat
then tl.item_ship_dat
end
from inserted i
inner join PS_TKT_HDR th
on i.STR_ID = th.STR_ID
and i.STA_ID = th.STA_ID
and i.TKT_NO = th.TKT_NO
inner join PS_TKT_LIN tl
on th.STR_ID = tl.STR_ID
and th.STA_ID = tl.STA_ID
and th.TKT_NO = tl.TKT_NO
and i.SEQ_NO = tl.SEQ_NO
inner join deleted d
on d.STR_ID = th.STR_ID
and d.STA_ID = th.STA_ID
and d.TKT_NO = th.TKT_NO

-- PS_TKT_HDR table:
CREATE TABLE test_PS_TKT_HDR
(
	STR_ID varchar(10),
	STA_ID varchar(10),
	TKT_NO varchar(15),
	CONSTRAINT PK_test_PS_TKT_HDR PRIMARY KEY CLUSTERED(STR_ID,STA_ID,TKT_NO),
	SHIP_DAT datetime
)

-- PS_TKT_LIN table:
CREATE TABLE test_PS_TKT_LIN
(
	STR_ID varchar(10),
	STA_ID varchar(10),
	TKT_NO varchar(15),
	SEQ_NO int,
	CONSTRAINT PK_test_PS_TKT_LIN PRIMARY KEY CLUSTERED(STR_ID,STA_ID,TKT_NO,SEQ_NO),
	CONSTRAINT FK_test_PS_TKT_HDR FOREIGN KEY(STR_ID,STA_ID,TKT_NO) REFERENCES test_PS_TKT_HDR(STR_ID,STA_ID,TKT_NO),
	ITEM_SHIP_DAT datetime
)
GO

ALTER TRIGGER trgtest_PS_TKT_HDR ON test_PS_TKT_HDR FOR UPDATE
AS
BEGIN
	/*
	When SHIP_DAT is updated in table test_PS_TKT_HDR
	then update ITEM_SHIP_DAT in table test_PS_TKT_LIN under following condidtions:
		1. There is a non NULL SHIP_DAT value in table test_PS_TKT_HDR
		2. If ln.ITEM_SHIP_DAT is NULL then set it to hdr.SHIP_DAT
		3. If ln.ITEM_SHIP_DAT is NOT NULL then only update where the old hdr.SHIP_DAT matches ln.ITEM_SHIP_DAT
	*/
	IF UPDATE(SHIP_DAT)
		UPDATE ln SET
			ln.ITEM_SHIP_DAT = hdr.SHIP_DAT
		FROM
			test_PS_TKT_LIN ln
			JOIN test_PS_TKT_HDR hdr
				ON ln.STR_ID = hdr.STR_ID
				AND ln.STA_ID = hdr.STA_ID
				AND ln.TKT_NO = hdr.TKT_NO
			JOIN deleted d
				ON ln.STR_ID = d.STR_ID
				AND ln.STA_ID = d.STA_ID
				AND ln.TKT_NO = d.TKT_NO
		WHERE
			hdr.SHIP_DAT IS NOT NULL
			AND
			( d.SHIP_DAT = ln.ITEM_SHIP_DAT OR ln.ITEM_SHIP_DAT IS NULL )
END
GO

CREATE TRIGGER trgtest_PS_TKT_LIN ON test_PS_TKT_LIN FOR INSERT
AS
BEGIN
	/*
	When a row is inserted in table test_PS_TKT_LIN
	Set the ITEM_SHIP_DAT to SHIP_DAT from table test_PS_TKT_HDR
	provided that:
		a) no value was explicitly provided for ITEM_SHIP_DAT
		b) there is a non NULL SHIP_DAT value in table test_PS_TKT_HDR
	*/
	UPDATE ln SET
		ln.ITEM_SHIP_DAT = hdr.SHIP_DAT
	FROM
		test_PS_TKT_LIN ln
		JOIN test_PS_TKT_HDR hdr
			ON ln.STR_ID = hdr.STR_ID
			AND ln.STA_ID = hdr.STA_ID
			AND ln.TKT_NO = hdr.TKT_NO
		/*JOIN inserted i
			ON ln.STR_ID = i.STR_ID
			AND ln.STA_ID = i.STA_ID
			AND ln.TKT_NO = i.TKT_NO
			AND ln.SEQ_NO = i.SEQ_NO*/
	WHERE
		ln.ITEM_SHIP_DAT IS NULL
		AND hdr.SHIP_DAT IS NOT NULL
END
GO


-- Cleanup
DROP TABLE test_PS_TKT_LIN
DROP TABLE test_PS_TKT_HDR

CREATE TRIGGER trgtest_PS_TKT_LIN ON test_PS_TKT_LIN FOR INSERT
AS
BEGIN
	/*
	When a row is inserted in table test_PS_TKT_LIN
	Set the ITEM_SHIP_DAT to SHIP_DAT from table test_PS_TKT_HDR
	provided that:
		a) no value was explicitly provided for ITEM_SHIP_DAT
		b) there is a non NULL SHIP_DAT value in table test_PS_TKT_HDR
	*/
	UPDATE ln SET
		ln.ITEM_SHIP_DAT = hdr.SHIP_DAT
	FROM
		test_PS_TKT_LIN ln
	           JOIN test_PS_TKT_HDR hdr
			ON ln.STR_ID = hdr.STR_ID
			AND ln.STA_ID = hdr.STA_ID
			AND ln.TKT_NO = hdr.TKT_NO
		/*JOIN inserted i
			ON ln.STR_ID = i.STR_ID
			AND ln.STA_ID = i.STA_ID
			AND ln.TKT_NO = i.TKT_NO
			AND ln.SEQ_NO = i.SEQ_NO*/
	WHERE
		ln.ITEM_SHIP_DAT IS NULL
		AND hdr.SHIP_DAT IS NOT NULL

set nocount on
IF UPDATE(SHIP_DAT)
		UPDATE ln SET
			ln.ITEM_SHIP_DAT = hdr.SHIP_DAT
		FROM
	              test_PS_TKT_LIN ln
			JOIN test_PS_TKT_HDR hdr
				ON ln.STR_ID = hdr.STR_ID
				AND ln.STA_ID = hdr.STA_ID
				AND ln.TKT_NO = hdr.TKT_NO
			JOIN deleted d
				ON ln.STR_ID = d.STR_ID
				AND ln.STA_ID = d.STA_ID
				AND ln.TKT_NO = d.TKT_NO
		WHERE
			hdr.SHIP_DAT IS NOT NULL
			AND
			( d.SHIP_DAT = ln.ITEM_SHIP_DAT OR ln.ITEM_SHIP_DAT IS NULL )
set nocount off

set nocount on
IF UPDATE(SHIP_DAT)
		UPDATE ln SET
			ln.ITEM_SHIP_DAT = hdr.SHIP_DAT
		FROM
	              test_PS_TKT_LIN ln
			JOIN test_PS_TKT_HDR hdr
				ON ln.STR_ID = hdr.STR_ID
				AND ln.STA_ID = hdr.STA_ID
				AND ln.TKT_NO = hdr.TKT_NO
			JOIN deleted d
				ON ln.STR_ID = d.STR_ID
				AND ln.STA_ID = d.STA_ID
				AND ln.TKT_NO = d.TKT_NO
		WHERE
			hdr.SHIP_DAT IS NOT NULL
			AND
			( d.SHIP_DAT = ln.ITEM_SHIP_DAT OR ln.ITEM_SHIP_DAT IS NULL )
set nocount off

quote:

---

Apparently the deleted.tkt_ship_dat NEVER equals ITEM_SHIP_DAT,
even when they do...

---

quote:

---

changing ln. to hdr. won't make any difference as the values are equal from the previous join and it's the pk on hdr - but the trigger is on hdr so you should join deleted to hdr.

---

quote:

---

The only way chamging the last line could make it work is if the ln.ITEM_SHIP_DAT is not null and the old hdr.SHIP_DAT isn't equal to it.
Note this will not update if the old hdr.SHIP_DAT was null and ln.ITEM_SHIP_DAT is not null.

---

quote:

---

When you check the dates to see if it should update are you also checking the miilliseconds?

---

update ps_tkt_hdr
set ship_dat='12/31/2004'
where tkt_no='6696'

CREATE TRIGGER updateitemsd ON [dbo].[PS_TKT_LIN] 
FOR INSERT, UPDATE, DELETE 
AS
/* When SHIP_DAT is updated in table PS_TKT_HDR then update ITEM_SHIP_DATE
 in table PS_TKT_LIN under the following conditions:
a) There is a non-null SHIP_DAT value in PS_TKT_HDR
b) If tl.ITEM_SHIP_DAT is NULL then set it to th.SHIP_DAT
c) If tl.ITEM_SHIP_DAT is NOT NULL then only update if it matches old th.SHIP_DAT
*/
set nocount on
if update(TKT_SHIP_DAT)
update tl set
tl.ITEM_SHIP_DAT=tl.TKT_SHIP_DAT
from PS_TKT_LIN tl

join deleted d
on tl.STR_ID=d.STR_ID
and tl.STA_ID=d.STA_ID
and tl.TKT_NO=d.TKT_NO
where
tl.TKT_SHIP_DAT is not null
and (d.TKT_SHIP_DAT=tl.ITEM_SHIP_DAT or tl.item_ship_dat is null)

set nocount off

-------------------------------------------------------------------

CREATE trigger tr_UpdateItemShipDate
on dbo.PS_TKT_LIN
for insert
as
set nocount on

/*When a row is inserted in table PS_TKT_LIN
set the ITEM_SHIP_DAT to PS_TKT_HDR.SHIP_DAT provided that:
a) no value was explicitly provided for ITEM_SHIP_DAT
b) there is a non-null SHIP_DAT value in PS_TKT_HDR
*/

update tl
set tl.TKT_SHIP_DAT = th.SHIP_DAT

from  PS_TKT_LIN tl
 join PS_TKT_HDR th
on tl.STR_ID = th.STR_ID
and tl.STA_ID = th.STA_ID
and tl.TKT_NO = th.TKT_NO