WDCM推荐的触发器
--触发器根据来源稿件的自定义属性(domainname)更新到推荐对象
--使前缀加上网站全路经
--推荐稿件对象表userobject6
--需要配合自定义函数得到2级自定义属性domainname名
CREATE TRIGGER [insert_suggest] ON [userobject6]
FOR INSERT
AS
DECLARE @link VARCHAR(256),
@id INT,
@domainname1 VARCHAR(1000),
@domainname VARCHAR(1000),
@domainname2 VARCHAR(1000),
@userprop2 VARCHAR(1000)
IF UPDATE(userprop3)
BEGIN
SELECT @link = userprop3,@domainname1 = userprop1,@userprop2 = userprop2,@id = idleaf FROM inserted
IF @@ROWCOUNT > 0
BEGIN
SELECT @domainname = dbo.uf_Getn1(@domainname1) FROM userobject6
IF @@ROWCOUNT > 0 and @domainname is not null and @domainname<>''
BEGIN
IF RIGHT(@domainname,1)='/'
set @domainname2 = SUBSTRING(@domainname,1,(LEN(@domainname)-1))
ELSE
set @domainname2 = @domainname
UPDATE userobject6 SET userprop2=@domainname2+'/'+@userprop2 WHERE idleaf = @id
END
END
END
--此自定义函数是根据栏目路径获取二级自定义属(domainname)名
--如输入值:‘/卫生局/头条新闻’
CREATE FUNCTION uf_Getn1 (@p_pathname VARCHAR(1000))
RETURNS VARCHAR(500)
AS
BEGIN
-- 如果出现错误,则返回/或是NULL
-- SQL 写法
DECLARE @v_NodeName varchar(200),
@v_Node varchar(1000),
@v_nodepath varchar(1000),
@first INT,
@second INT
set @v_NodeName = ''
set @v_Node = substring(@p_pathname,2,500)
IF @v_Node = ''
BEGIN
set @v_NodeName = 'null'
RETURN (@v_NodeName)
END
SELECT @first = CHARINDEX('/', @v_Node)
SELECT @second = CHARINDEX('/', @v_Node, @first + 1)
IF @second>0
SELECT @v_nodepath = SUBSTRING(@v_Node, 1, @second)
ELSE
SELECT @v_nodepath = @v_Node+'/'
BEGIN
set @v_NodeName = (SELECT userprop1
FROM NodeTable
where dbo.uf_getpath(idnode) = @v_nodepath)
if @@rowcount = 0
RETURN 'null'
END
RETURN @v_NodeName
END