if exists (select name from sysobjects where name = 'hn_LinkCheckExternal_DeleteTarget' and type = 'TR')
drop trigger hn_LinkCheckExternal_DeleteTarget
go
/*
** Triggers deletion of targets of already existing links
*/
create trigger hn_LinkCheckExternal_DeleteTarget
on HDS_TOPIC
after delete
as
declare @DEL_PUB_UNID varchar(64)
declare @DEL_DOC_HEADING nvarchar(254)
declare @TARGET_EFFECTIVE bit
declare @TARGET_AUDIENCE varchar(32)
declare @TARGET_PUB_TITLE nvarchar(254)
declare @TARGET_LANGUAGE nvarchar(128)
declare @TARGET_PUB_CATEGORY nvarchar(254)
-- fetch topic params
select
@DEL_PUB_UNID = PUB_UNID,
@DEL_DOC_HEADING = DOC_HEADING
from deleted
-- fetch publication params
select
@TARGET_EFFECTIVE = EFFECTIVE,
@TARGET_AUDIENCE = AUDIENCE,
@TARGET_PUB_TITLE = PUB_TITLE,
@TARGET_LANGUAGE = LANGUAGE,
@TARGET_PUB_CATEGORY = PUB_CATEGORY
from HDS_PUBLICATION
where PUB_UNID = @DEL_PUB_UNID
-- do nothing if noneffective or published in Approval
if @TARGET_EFFECTIVE = 0 return
if @TARGET_AUDIENCE = 'Approval' return
declare @SOURCE_DOC_UNID varchar(64)
declare @SOURCE_SEQNUM smallint
declare @SOURCE_LINK_TYPE varchar(16)
declare @SOURCE_LINK_CONFIGTYPE varchar(16)
declare @SOURCE_PARAM1 nvarchar(384)
declare @SOURCE_PARAM2 nvarchar(384)
declare @SOURCE_PARAM3 nvarchar(384)
declare @SOURCE_PARAM4 nvarchar(384)
-- select the pub link infos which match the params
declare PUB_LINK_INFO_CURSOR cursor for
select DOC_UNID, SEQNUM, LINK_TYPE, LINK_CONFIGTYPE, PARAM1, PARAM2, PARAM3, PARAM4
from HDS_PUB_LINK_INFO
where
(
LINK_CONFIGTYPE='*' and
(PARAM1=DB_NAME() or PARAM1='') and
(PARAM2=@TARGET_PUB_TITLE or PARAM3=@DEL_DOC_HEADING)
)
or
(
LINK_CONFIGTYPE='CustomLink' and
PARAM1=@TARGET_PUB_TITLE and
PARAM2=@DEL_DOC_HEADING and
PARAM3=@TARGET_LANGUAGE and
PARAM4=@TARGET_PUB_CATEGORY
)
open PUB_LINK_INFO_CURSOR
fetch next
from PUB_LINK_INFO_CURSOR
into
@SOURCE_DOC_UNID,
@SOURCE_SEQNUM,
@SOURCE_LINK_TYPE,
@SOURCE_LINK_CONFIGTYPE,
@SOURCE_PARAM1,
@SOURCE_PARAM2,
@SOURCE_PARAM3,
@SOURCE_PARAM4
while @@FETCH_STATUS = 0 begin
EXECUTE hn_LinkCheckExternal
@SOURCE_DOC_UNID OUTPUT,
@SOURCE_SEQNUM OUTPUT,
@SOURCE_LINK_TYPE OUTPUT,
@SOURCE_LINK_CONFIGTYPE OUTPUT,
@SOURCE_PARAM1 OUTPUT,
@SOURCE_PARAM2 OUTPUT,
@SOURCE_PARAM3 OUTPUT,
@SOURCE_PARAM4 OUTPUT
fetch next
from PUB_LINK_INFO_CURSOR
into
@SOURCE_DOC_UNID,
@SOURCE_SEQNUM,
@SOURCE_LINK_TYPE,
@SOURCE_LINK_CONFIGTYPE,
@SOURCE_PARAM1,
@SOURCE_PARAM2,
@SOURCE_PARAM3,
@SOURCE_PARAM4
end
close PUB_LINK_INFO_CURSOR
deallocate PUB_LINK_INFO_CURSOR