--触发器根据来源稿件的自定义属性（domainname）更新到推荐对象
--使前缀加上网站全路经
--推荐稿件对象表userobject6
--需要配合自定义函数得到２级自定义属性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