liquibase - 运行 Liquibase : Incorrect syntax near 'up' 时出现意外错误

Question

我正在使用 liquibase 部署以下 SQL 代码:

-- updated Record Permissions trigger
USE [ITAM]
GO
/****** Object:  Trigger [dbo].[tr_tblRecordPermissions]    Script Date: 03/04/2016 22:10:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tr_tblRecordPermissions] ON [dbo].[tblRecordPermissions] INSTEAD OF UPDATE, DELETE AS
BEGIN
    SET NOCOUNT ON


    --
    -- Use a variable to capture current date/time and re-use
    -- We don't want to repeatedly call getDate() as the time will drift
    -- and it'll look like we updated different records at different times
    -- depending on how long it takes to execute the portion of the trigger
    -- that records changes in the audit table
    --
    DECLARE @now AS DATETIME

    SET @now = getDate()

    -- Determine what action invoked the trigger
    -- Depending on the action, we'll need to evaluate records in inserted, deleted, or both
    DECLARE @actionType CHAR(1)

    IF ( EXISTS ( SELECT * FROM inserted ) )
        IF ( EXISTS ( SELECT * FROM deleted ) )
            SET @actionType = 'U' -- Update
        ELSE
            SET @actionType = 'I' -- Insert
    ELSE
        SET @actionType = 'D' -- Delete

    -- Prepare a temp table of changed data, to be used for audit and for record-level security checks
    --    CONVERT all values into the same data type (nvarchar(255)) in preparation for capturing changes in the audit table
    --    Also, CONVERT ID values into text strings
    SELECT source,
        ID,
        userId,
        ug.shortName AS userGroup,
        CAST(canEdit AS NVARCHAR(255)) canEdit,
        CAST(canAdministerPermissions AS NVARCHAR(255)) canAdministerPermissions,
        CAST(canAdministerBusinessManaged AS NVARCHAR(255)) canAdministerBusinessManaged,
        CAST(rp.comments AS NVARCHAR(255)) comments
    INTO #tmpDataToInspectForChanges
    FROM (
        SELECT 'old' AS source,
            old.*
        FROM deleted old /* handling the new & old at once, simply to avoid a lot of copy/paste code */

        UNION ALL

        SELECT 'new' AS source,
            new.*
        FROM inserted new
        ) rp
    LEFT JOIN tblUserGroups ug
        ON ug.groupId = rp.groupId

    -- Pivot the data.  Each field value will become a separate record
    -- ITAM uses a single generic audit table that captures field level changes.  Other processes can easily check
    -- to see if individual field values have changed (e.g. check for changes to record ownership for any type of record, etc)
    SELECT source,
        ID,
        field,
        value
    INTO #pivotData
    FROM #tmpDataToInspectForChanges
    UNPIVOT(value FOR field IN (
                userId,
                userGroup,
                canEdit,
                canAdministerPermissions,
                canAdministerBusinessManaged,
                comments
                )) AS up;

    --
    -- Determine what specific field values have changed
    --
    SELECT @now AS DATETIME,
        dbo.getCurrentUser() AS userName,
        'tblRecordPermissions' AS formName,
        CASE @actionType
            WHEN 'U' THEN 'EDIT'
            WHEN 'I' THEN 'INSERT'
            ELSE 'DELETE'
            END AS action,
        IsNULL(old.ID, new.ID) AS recordId,
        IsNULL(old.field, new.field) AS fieldName,
        old.value AS oldValue,
        new.value AS newValue,
        NULL AS comments
    INTO #tmpAuditTrail
    FROM (
        SELECT *
        FROM #pivotData
        WHERE source = 'old'
        ) old
    FULL JOIN (
        SELECT *
        FROM #pivotData
        WHERE source = 'new'
        ) new
        ON old.ID = new.ID
            AND
            old.field = new.field
    WHERE IsNULL(old.value, '') <> IsNULL(new.value, '')
        OR @actionType = 'D'

    -- 
    -- Now that we know what is changing we need to verify permissions for the affected records
--   special case: if the recordOwner field changes, consider entitlements based on the 'old' recordOwnerId
--   special case: Members of the SA team are entitled to change server records whereManagedBy = IBM or SA, regardless of recordOwnerId
--   special case: Members of the SD team are entitled to change server records whereManagedBy = SD, regardless of recordOwnerId
    --
    -- The record-level entitlements values we need to check are found in...
    --     a) deleted, for records to be removed or for records that are being updated/modified (because we need to consider permissions based on original values)
    --     b) inserted, for new records
    --
    DECLARE @isInsert TINYINT

    SET @isInsert = (CASE @actionType WHEN 'I' THEN 1 ELSE 0 END)

    SELECT
        (CASE @isInsert WHEN 1 THEN i.groupId ELSE d.groupId END) AS groupId
    INTO #tmpRecordPermissionsToCheck
    FROM inserted i
    FULL JOIN deleted d
        ON i.userId = d.userId
            AND
            i.groupId = d.groupId

-- Stop everything if the user is attempting to edit something they're not entitled to...
--   special case(s): refer above for additional tblServer-specific checks required here
    DECLARE @errMsg VARCHAR(255)

    SELECT @errMsg = 'You do not have permission to edit permissions for group ' + IsNULL(ug.shortName, '')
    FROM #tmpRecordPermissionsToCheck tmp
    LEFT JOIN tblUserGroups ug
        ON ug.groupId = tmp.groupId
WHERE dbo.hasAdministrativePermissionsForGroup(tmp.groupId, dbo.getCurrentUser()) = 0

    IF (@errMsg IS NOT NULL)
    BEGIN
        RAISERROR ( @errMsg, 16, 1 )
        ROLLBACK TRANSACTION
        RETURN
    END

    -- At this point the user has permissions on tblRecordPermissions so we can commit the transactions
    IF (@actionType = 'I')
        INSERT INTO
            tblRecordPermissions
            (
                userId,
                groupId,
                canEdit,
                canAdministerPermissions,
                canAdministerBusinessManaged,
                comments,
                lastModifiedDate,
                lastModifiedBy
            )
            SELECT
                userId,
                groupId,
                canEdit,
                canAdministerPermissions,
                canAdministerBusinessManaged,
                comments,
                lastModifiedDate,
                lastModifiedBy
            FROM
                inserted
    ELSE
        IF (@actionType = 'D')
            DELETE tblRecordPermissions WHERE ID IN (SELECT ID FROM deleted)
        ELSE
        BEGIN

            --remove old records
            DELETE tblRecordPermissions WHERE ID IN (SELECT ID FROM deleted)

            -- Update with new records
            INSERT INTO
                tblRecordPermissions
                (
                    userId,
                    groupId,
                    canEdit,
                    canAdministerPermissions,
                    canAdministerBusinessManaged,
                    comments,
                    lastModifiedDate,
                    lastModifiedBy
                )
                SELECT
                    userId,
                    groupId,
                    canEdit,
                    canAdministerPermissions,
                    canAdministerBusinessManaged,
                    comments,
                    lastModifiedDate,
                    lastModifiedBy
                FROM
                    inserted

        END

    IF (
            EXISTS (
                SELECT *
                FROM #tmpAuditTrail
                )
            )
    BEGIN
        INSERT INTO tblAuditTrail (
            DATETIME,
            userName,
            formName,
            action,
            recordId,
            fieldName,
            oldValue,
            newValue,
            comments
            )
        SELECT *
        FROM #tmpAuditTrail

        -- Auto-set the lastModifiedBy, lastModifiedDate, regardless of what the user specified
        UPDATE tblRecordPermissions
        SET lastModifiedBy = dbo.getCurrentUser(),
            lastModifiedDate = @now
        FROM tblRecordPermissions
        INNER JOIN inserted
            ON inserted.userId = tblRecordPermissions.userId
                AND
                inserted.groupId = tblRecordPermissions.groupId
    END
END

从命令行运行时，出现以下错误 -

Unexpected error running Liquibase: Incorrect syntax near 'up'.

这是我的 liquibase 文件 -

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">
    <changeSet id="16" author="name">
        <sqlFile path="sql/db.changelog-1.16.sql" relativeToChangelogFile="true" />    
        <rollback>
            <sqlFile path="sql/rollback/rollback_db.changelog-1.16.sql" relativeToChangelogFile="true" />
        </rollback>
        <comment>Alter Record Permissions trigger</comment>
    </changeSet>
</databaseChangeLog>

我尝试输入以下内容 -
splitStatements="false" endDelimiter="GO"
现在我收到一条不同的消息:
Unexpected error running Liquibase: Incorrect syntax near 'GO'.

Answer 1

这个问题是通过两种方式解决的。

附加 ;在 SQL 文件中触发逻辑的末尾

添加 splitStatements="false" endDelimiter=";"到 sqlFile 参数