Reputation: 1149

SQL Syntax Failure - Experienced SQL Eyes Needed

I have a piece of software that provides SQL data from the backend of an old Linux server we use in the business.

Every now and then we have to do a 'BULK INSERT' of this data into our SQL, what this does is DROPS the relevant tables and anything attached (Triggers etc.) and rebuilds them and repopulates the data.

The software provides a Pre-SQL and Post-SQL 'Scripting' area where you can place your own scripts. I would like to place a script in there however it always fails with a syntax issue.

I literally copy and paste below:

CREATE TRIGGER [dbo].[Tr_icp_Dapolicy_Insert] 
ON  [dbo].[icp_Dapolicy] 
AFTER INSERT
AS 
BEGIN
    INSERT INTO T_FIG_PreDialler (Branch@, Key@, PolicyRef@, ClientRef@, Create_Date, Create_Time, Copied, CopyDateTime) 
        SELECT 
            Branch@, Key@, PolicyRef@, ClientRef@, Create_date, Create_time, 0, GETDATE() 
        FROM 
            INSERTED
        WHERE 
            Branch@ <>1
END

CREATE TRIGGER [dbo].[Tr_icp_Dapolicy_Update] 
ON  [dbo].[icp_Dapolicy] 
AFTER UPDATE
AS 
BEGIN
    IF UPDATE(#Exec)
    BEGIN
        INSERT INTO T_FIG_PreDiallerUpdates (Branch@, Key@, NewExec, OldExec) 
            SELECT 
                INSERTED.Branch@, INSERTED.Key@, INSERTED.#Exec, DELETED.#Exec 
            FROM 
                INSERTED
            INNER JOIN 
                DELETED ON INSERTED.Branch@ = DELETED.Branch@
                        AND INSERTED.Key@ = DELETED.Key@
            WHERE 
                ((INSERTED.#Exec = ''9999'') AND (DELETED.#Exec <> ''9999'')) 
                OR ((INSERTED.#Exec <> ''9999'') AND (DELETED.#Exec = ''9999''))
                AND (INSERTED.Branch@ in (0, 2))
    END
END

CREATE TRIGGER [dbo].[Tr_icp_brcledger_Insert] 
ON  [dbo].[icp_brcledger] 
AFTER INSERT
AS 
BEGIN
    INSERT INTO T_FIG_Trigger_Ledger_Add (Branch@, Key@, PolicyRef@, ClientRef@, [Suffix@],
                [Polref], [Poltype], [Suffix], [Trantype], [Dt_raised],[Status], [Ledger_dt], [Remarks_4], 
                [#Exec], [Datecreated], [Operator], [LastUpdatedTime])
        SELECT 
            Branch@, Key@, PolicyRef@, ClientRef@, [Suffix@], 
            [Polref], [Poltype], [Suffix], [Trantype], [Dt_raised], [Status], [Ledger_dt], [Remarks_4],
            [#Exec], [Datecreated], [Operator], [LastUpdatedTime]
        FROM 
            INSERTED
        WHERE 
            (INSERTED.Trantype IN (''New Business'', ''Renewal'', ''Transfrd NB''))
            OR ((INSERTED.Branch@ = 2) 
              AND (INSERTED.Poltype = ''LE'')
              AND (INSERTED.Trantype = ''Charge''))
END

CREATE TRIGGER [dbo].[Tr_icp_brcledger_Update] 
ON  [dbo].[icp_brcledger] 
AFTER UPDATE
AS 
BEGIN
    DECLARE @Now DateTime;
    SET @Now = GETDATE();

    INSERT INTO T_FIG_Trigger_Ledger_Update
              (Branch@, Key@, PolicyRef@_Before, Poltype_Before, Trantype_Before, Ledger_dt_Before, 
                    Remarks_4_Before, #Exec_Before, Datecreated_Before, Operator_Before, 
                    LastUpdatedTime_Before, PolicyRef@_After, Poltype_After, Trantype_After, Ledger_dt_After, 
                    Remarks_4_After, #Exec_After, Datecreated_After, Operator_After, 
                    LastUpdatedTime_After, Copied, CopyDateTime, 
                    Orig_debt_Before, Comm_amt_Before, Orig_debt_After, Comm_amt_After)
        SELECT 
            INSERTED.[Branch@], INSERTED.[Key@],
            DELETED.[PolicyRef@] AS [PolicyRef@_Before],
            DELETED.[Poltype] AS [Poltype_Before],
            DELETED.[Trantype] AS [Trantype@_Before],
            DELETED.[Ledger_dt] AS [Ledger_dt_Before],
            DELETED.[Remarks_4] AS [Remarks_4_Before],
            DELETED.[#Exec] AS [#Exec_Before],
            DELETED.[Datecreated] AS [Datecreated_Before],
            DELETED.[Operator] AS [Operator_Before],
            DELETED.[LastUpdatedTime] AS [LastUpdatedTime_Before],
            INSERTED.[PolicyRef@] AS [PolicyRef@_After],
            INSERTED.[Poltype]AS [Poltype_After],
            INSERTED.[Trantype]AS [Trantype@_After],
            INSERTED.[Ledger_dt] AS [Ledger_dt_After],
            INSERTED.[Remarks_4] AS [Remarks_4_After],
            INSERTED.[#Exec] AS [#Exec_After],
            INSERTED.[Datecreated] AS [Datecreated_After],
            INSERTED.[Operator] AS [Operator_After],
            INSERTED.[LastUpdatedTime] AS [LastUpdatedTime_After],
            0, @Now,
            DELETED.Orig_debt AS [Orig_debt_Before],
            DELETED.Comm_amt AS [Comm_amt_Before],
            INSERTED.Orig_debt AS [Orig_debt_After],
            INSERTED.Comm_amt AS [Comm_amt_After]
        FROM 
            DELETED 
        INNER JOIN 
            INSERTED ON DELETED.Branch@ = INSERTED.Branch@ AND DELETED.Key@ = INSERTED.Key@
        WHERE 
            (INSERTED.PolicyRef@ <> DELETED.PolicyRef@
            OR INSERTED.Poltype <> DELETED.Poltype
            OR INSERTED.Trantype <> DELETED.Trantype
            OR INSERTED.Ledger_dt  <> DELETED.Ledger_dt
            OR INSERTED.Ledger_dt is null and DELETED.Ledger_dt is not null
            OR INSERTED.Ledger_dt is not null and DELETED.Ledger_dt is null
            OR ISNULL(INSERTED.Remarks_4, ''NULL'') <> isnull(DELETED.Remarks_4, ''NULL'')
            OR ISNULL(INSERTED.#Exec, ''NULL'') <> isnull(DELETED.#Exec, ''NULL'')
            OR ISNULL(INSERTED.Operator, ''NULL'') <> isnull(DELETED.Operator, ''NULL'')
            OR INSERTED.Operator <> DELETED.Operator
            OR INSERTED.Orig_debt <> DELETED.Orig_debt
            OR INSERTED.Comm_amt <> DELETED.Comm_amt)
            AND (INSERTED.Trantype <> ''Journal'')
END

CREATE TRIGGER [dbo].[Tr_icp_brcledger_Delete] 
ON  [dbo].[icp_brcledger] 
AFTER DELETE
AS 
BEGIN
    INSERT INTO T_FIG_Trigger_Ledger_Delete (Branch@, Key@,PolicyRef@, TranType, PolType)
        SELECT 
            Branch@, Key@,PolicyRef@, Trantype, Poltype
        FROM 
            DELETED
        WHERE 
            (DELETED.Trantype <> ''Journal'')
END

When it attempts to then run this at the end of the BULK INSERT I get errors like:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'TRIGGER'.

Now I'm not asking for help on the software, my question is this:

What syntax would exist for the above to fail? I can run this in a SQL Query Window and its successful, what have these software developers coded 'before' this postdone-sql script runs for it to cause a syntax error?

Thanks for any and all help about this, because the software vendor themselves have little to no clue and haven't been able to answer this for 2 years.

UPDATE

Attempted to wrap the whole thing in a var char and exec it and this also failed

 DECLARE @CreateTriggers VARCHAR(MAX);

    SET @CreateTriggers = 'CREATE TRIGGER [dbo].[Tr_icp_Dapolicy_Insert] 
                       ON  [dbo].[icp_Dapolicy] 
                       AFTER INSERT
                    AS 
                    BEGIN
                                    INSERT INTO T_FIG_PreDialler (Branch@, Key@, PolicyRef@, ClientRef@, Create_Date, Create_Time, Copied, CopyDateTime) 
                                                    SELECT Branch@, Key@, PolicyRef@, ClientRef@, Create_date, Create_time, 0, GETDATE() FROM INSERTED
                                                    WHERE Branch@ <>1

                    END'

    EXEC(@CreateTriggers);

Upvotes: 1

Answers (2)

Fuzzy

Reputation: 3810

This should work, you were just missing the GO statement after each CREATE statement. Each trigger creation script has to be within its own batch:

CREATE TRIGGER [dbo].[Tr_icp_Dapolicy_Insert] 
ON  [dbo].[icp_Dapolicy] 
AFTER INSERT
AS 
BEGIN
    INSERT INTO T_FIG_PreDialler (Branch@, Key@, PolicyRef@, ClientRef@, Create_Date, Create_Time, Copied, CopyDateTime) 
        SELECT 
            Branch@, Key@, PolicyRef@, ClientRef@, Create_date, Create_time, 0, GETDATE() 
        FROM 
            INSERTED
        WHERE 
            Branch@ <>1
END
GO

CREATE TRIGGER [dbo].[Tr_icp_Dapolicy_Update] 
ON  [dbo].[icp_Dapolicy] 
AFTER UPDATE
AS 
BEGIN
    IF UPDATE(#Exec)
    BEGIN
        INSERT INTO T_FIG_PreDiallerUpdates (Branch@, Key@, NewExec, OldExec) 
            SELECT 
                INSERTED.Branch@, INSERTED.Key@, INSERTED.#Exec, DELETED.#Exec 
            FROM 
                INSERTED
            INNER JOIN 
                DELETED ON INSERTED.Branch@ = DELETED.Branch@
                        AND INSERTED.Key@ = DELETED.Key@
            WHERE 
                ((INSERTED.#Exec = ''9999'') AND (DELETED.#Exec <> ''9999'')) 
                OR ((INSERTED.#Exec <> ''9999'') AND (DELETED.#Exec = ''9999''))
                AND (INSERTED.Branch@ in (0, 2))
    END
END
GO

CREATE TRIGGER [dbo].[Tr_icp_brcledger_Insert] 
ON  [dbo].[icp_brcledger] 
AFTER INSERT
AS 
BEGIN
    INSERT INTO T_FIG_Trigger_Ledger_Add (Branch@, Key@, PolicyRef@, ClientRef@, [Suffix@],
                [Polref], [Poltype], [Suffix], [Trantype], [Dt_raised],[Status], [Ledger_dt], [Remarks_4], 
                [#Exec], [Datecreated], [Operator], [LastUpdatedTime])
        SELECT 
            Branch@, Key@, PolicyRef@, ClientRef@, [Suffix@], 
            [Polref], [Poltype], [Suffix], [Trantype], [Dt_raised], [Status], [Ledger_dt], [Remarks_4],
            [#Exec], [Datecreated], [Operator], [LastUpdatedTime]
        FROM 
            INSERTED
        WHERE 
            (INSERTED.Trantype IN (''New Business'', ''Renewal'', ''Transfrd NB''))
            OR ((INSERTED.Branch@ = 2) 
              AND (INSERTED.Poltype = ''LE'')
              AND (INSERTED.Trantype = ''Charge''))
END
GO

CREATE TRIGGER [dbo].[Tr_icp_brcledger_Update] 
ON  [dbo].[icp_brcledger] 
AFTER UPDATE
AS 
BEGIN
    DECLARE @Now DateTime;
    SET @Now = GETDATE();

    INSERT INTO T_FIG_Trigger_Ledger_Update
              (Branch@, Key@, PolicyRef@_Before, Poltype_Before, Trantype_Before, Ledger_dt_Before, 
                    Remarks_4_Before, #Exec_Before, Datecreated_Before, Operator_Before, 
                    LastUpdatedTime_Before, PolicyRef@_After, Poltype_After, Trantype_After, Ledger_dt_After, 
                    Remarks_4_After, #Exec_After, Datecreated_After, Operator_After, 
                    LastUpdatedTime_After, Copied, CopyDateTime, 
                    Orig_debt_Before, Comm_amt_Before, Orig_debt_After, Comm_amt_After)
        SELECT 
            INSERTED.[Branch@], INSERTED.[Key@],
            DELETED.[PolicyRef@] AS [PolicyRef@_Before],
            DELETED.[Poltype] AS [Poltype_Before],
            DELETED.[Trantype] AS [Trantype@_Before],
            DELETED.[Ledger_dt] AS [Ledger_dt_Before],
            DELETED.[Remarks_4] AS [Remarks_4_Before],
            DELETED.[#Exec] AS [#Exec_Before],
            DELETED.[Datecreated] AS [Datecreated_Before],
            DELETED.[Operator] AS [Operator_Before],
            DELETED.[LastUpdatedTime] AS [LastUpdatedTime_Before],
            INSERTED.[PolicyRef@] AS [PolicyRef@_After],
            INSERTED.[Poltype]AS [Poltype_After],
            INSERTED.[Trantype]AS [Trantype@_After],
            INSERTED.[Ledger_dt] AS [Ledger_dt_After],
            INSERTED.[Remarks_4] AS [Remarks_4_After],
            INSERTED.[#Exec] AS [#Exec_After],
            INSERTED.[Datecreated] AS [Datecreated_After],
            INSERTED.[Operator] AS [Operator_After],
            INSERTED.[LastUpdatedTime] AS [LastUpdatedTime_After],
            0, @Now,
            DELETED.Orig_debt AS [Orig_debt_Before],
            DELETED.Comm_amt AS [Comm_amt_Before],
            INSERTED.Orig_debt AS [Orig_debt_After],
            INSERTED.Comm_amt AS [Comm_amt_After]
        FROM 
            DELETED 
        INNER JOIN 
            INSERTED ON DELETED.Branch@ = INSERTED.Branch@ AND DELETED.Key@ = INSERTED.Key@
        WHERE 
            (INSERTED.PolicyRef@ <> DELETED.PolicyRef@
            OR INSERTED.Poltype <> DELETED.Poltype
            OR INSERTED.Trantype <> DELETED.Trantype
            OR INSERTED.Ledger_dt  <> DELETED.Ledger_dt
            OR INSERTED.Ledger_dt is null and DELETED.Ledger_dt is not null
            OR INSERTED.Ledger_dt is not null and DELETED.Ledger_dt is null
            OR ISNULL(INSERTED.Remarks_4, ''NULL'') <> isnull(DELETED.Remarks_4, ''NULL'')
            OR ISNULL(INSERTED.#Exec, ''NULL'') <> isnull(DELETED.#Exec, ''NULL'')
            OR ISNULL(INSERTED.Operator, ''NULL'') <> isnull(DELETED.Operator, ''NULL'')
            OR INSERTED.Operator <> DELETED.Operator
            OR INSERTED.Orig_debt <> DELETED.Orig_debt
            OR INSERTED.Comm_amt <> DELETED.Comm_amt)
            AND (INSERTED.Trantype <> ''Journal'')
END
GO

CREATE TRIGGER [dbo].[Tr_icp_brcledger_Delete] 
ON  [dbo].[icp_brcledger] 
AFTER DELETE
AS 
BEGIN
    INSERT INTO T_FIG_Trigger_Ledger_Delete (Branch@, Key@,PolicyRef@, TranType, PolType)
        SELECT 
            Branch@, Key@,PolicyRef@, Trantype, Poltype
        FROM 
            DELETED
        WHERE 
            (DELETED.Trantype <> ''Journal'')
END
GO

Upvotes: 0

codeulike

Reputation: 23064

It might be something to do with the software sending the statements via JDBC.

Or perhaps they are sending the pre/post scripts in a batch along with other stuff, so SQL Server complains because 'CREATE TRIGGER' must be first thing in a batch.

Other people with similar-ish problems have suggested the option of wrapping whole thing in an exec(' ') clause.

See Create mssql trigger from java program

and also this answer to another question: https://stackoverflow.com/a/951956/22194

Upvotes: 1

SQL Syntax Failure - Experienced SQL Eyes Needed

Answers (2)

Related Questions