Function: nornix_site.add_new_document_before_sibling

Information
DTD identifierDeterministicData accessMode
int(11)NOMODIFIES SQL DATA

Code

Add new document before sibling

CREATE DEFINER=`root`@`localhost` FUNCTION `add_new_document_before_sibling`(vSiblingNo INTEGER,
 vPathPart VARCHAR(45), vMenuName VARCHAR(80), vPageTitle VARCHAR(80),
 vAuthLevel INTEGER) RETURNS int(11)
    MODIFIES SQL DATA
    COMMENT 'Add new document before sibling'
BEGIN
 DECLARE vSiblingLft, vLevel INTEGER;
 SELECT Lft, `Level` INTO vSiblingLft, vLevel FROM document WHERE `No` = vSiblingNo;
 UPDATE document
  SET Lft = IF (Lft >= vSiblingLft, Lft + 2, Lft),
      Rgt = Rgt + 2
  WHERE Rgt > vSiblingLft ORDER BY Rgt DESC;
 INSERT INTO document (`Level`, AuthLevel, MenuName, PageTitle, PathPart, Lft, Rgt)
 VALUES (vLevel, vAuthLevel, vMenuName, vPageTitle, vPathPart, vSiblingLft, (vSiblingLft + 1));
 RETURN LAST_INSERT_ID();
END