create trigger [dbo].[TR_PDCOMPOSITESEGMENTTABLELIST_INS] on [dbo].[PDCOMPOSITESEGMENTTABLELIST]
for insert, update
as
set nocount on
if exists(select * from inserted)
begin
-- Need to see if this has a custom composite segment, if it does then this is considered a custom composite segment.
-- First those WITHOUT a custom segment
update dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT
set SEGMENTTYPECODE = 1
from dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT
inner join inserted
on PDACCOUNTTABLESAVAILABLEFORSEGMENT.ID = inserted.PDCOMPOSITESEGMENTID
where (not exists (select 1
from inserted as inserted_inner
inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT as PDACCOUNTTABLESAVAILABLEFORSEGMENT_INNER
on PDACCOUNTTABLESAVAILABLEFORSEGMENT_INNER.ID = inserted.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
where (inserted_inner.ID = inserted.ID) and
(PDACCOUNTTABLESAVAILABLEFORSEGMENT_INNER.SEGMENTTYPECODE = 2)));
-- Next those WITH a custom segment
update dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT
set SEGMENTTYPECODE = 2
from dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT
inner join inserted
on PDACCOUNTTABLESAVAILABLEFORSEGMENT.ID = inserted.PDCOMPOSITESEGMENTID
where (exists (select 1
from inserted as inserted_inner
inner join dbo.PDACCOUNTTABLESAVAILABLEFORSEGMENT as PDACCOUNTTABLESAVAILABLEFORSEGMENT_INNER
on PDACCOUNTTABLESAVAILABLEFORSEGMENT_INNER.ID = inserted.PDACCOUNTTABLESAVAILABLEFORSEGMENTID
where (inserted_inner.ID = inserted.ID) and
(PDACCOUNTTABLESAVAILABLEFORSEGMENT_INNER.SEGMENTTYPECODE = 2)));
end
|
Trigger Definition
