Синтаксическая ошибка инструкция create trigger должна быть единственной в пакете

If you use SSMS (or other similar tool) to run the code produced by this script, you will get exactly the same error. It could run all right when you inserted batch delimiters (GO), but now that you don’t, you’ll face the same issue in SSMS too.

On the other hand, the reason why you cannot put GO in your dynamic scripts is because GO isn’t a SQL statement, it’s merely a delimiter recognised by SSMS and some other tools. Probably you are already aware of that.

Anyway, the point of GO is for the tool to know that the code should be split and its parts run separately. And that, separately, is what you should do in your code as well.

So, you have these options:

• insert EXEC sp_execute @sql just after the part that drops the trigger, then reset the value of @sql to then store and run the definition part in its turn;

• use two variables, @sql1 and @sql2, store the IF EXISTS/DROP part into @sql1, the CREATE TRIGGER one into @sql2, then run both scripts (again, separately).

But then, as you’ve already found out, you’ll face another issue: you cannot create a trigger in another database without running the statement in the context of that database.

Now, there are 2 ways of providing the necessary context:

1) use a USE statement;

2) run the statement(s) as a dynamic query using EXEC targetdatabase..sp_executesql N'…'.

Obviously, the first option isn’t going to work here: we cannot add USE … before CREATE TRIGGER, because the latter must be the only statement in the batch.

The second option can be used, but it will require an additional layer of dynamicity (not sure if it’s a word). It’s because the database name is a parameter here and so we need to run EXEC targetdatabase..sp_executesql N'…' as a dynamic script, and since the actual script to run is itself supposed to be a dynamic script, it, therefore, will be nested twice.

So, before the (second) EXEC sp_executesql @sql; line add the following:

SET @sql = N'EXEC ' + @dbname + '..sp_executesql N'''
           + REPLACE(@sql, '''', '''''') + '''';

As you can see, to integrate the contents of @sql as a nested dynamic script properly, they must be enclosed in single quotes. For the same reason, every single quotation mark in @sql must be doubled (e.g. using the REPLACE() function, as in the above statement).

Ok … I’m struggling to find a way to do this without having an exec wrapped around an exec. The code is already ugly enough having this in dynamic, but until I change the way I sync all my admin db’s, this is my best bet. Modified a known process out there slightly to capture DDL events, and I need to push the trigger to all databases.

1) Why can you not check for trigger existance with OBJECT_ID? Look how I had to find it, not how I would normally do it.

2) Without dropping/recreating the object every time, how can I use a database and create an object?

Thanks in advance

SET NOCOUNT ON

-- Setup DDLChangeLog Table

IF OBJECT_ID('DDLChangeLog','U') IS NULL

BEGIN

CREATE TABLE [dbo].[DDLChangeLog]

(

[LogId]INTIDENTITY(1,1) CONSTRAINT PK_DDLChangeLog PRIMARY KEY CLUSTERED

,[DatabaseName] VARCHAR(256)

,[EventType]VARCHAR(50)

,[ObjectName]VARCHAR(256)

,[ObjectType]VARCHAR(25)

,[SqlCommand]VARCHAR(MAX)

,[EventDate]DATETIME CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (GETDATE())

,[LoginName]VARCHAR(256)

,[ProgramName]VARCHAR(256)

)

GRANT INSERT ON [dbo].[DDLChangeLog] TO [public]

END

-- Push Trigger to all databases

IF OBJECT_ID('tempdb.dbo.#systriggers') IS NOT NULL

DROP TABLE #systriggers

SELECT TOP 0 * INTO #systriggers FROM [sys].[triggers]

DECLARE

@DBNameSYSNAME

,@LoggingDBNameSYSNAME

SET @LoggingDBName = 'admin'

DECLARE #dbs CURSOR STATIC LOCAL FOR

SELECT [name] FROM [master].[sys].[databases]

WHERE [source_database_id] IS NULL

AND [name] != 'tempdb'

AND [name] = 'admin'

ORDER BY [name]

OPEN #dbs

FETCH NEXT FROM #dbs INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

-- Lame way of checking to see if trigger exists as OBJECT_ID does not work for triggers

TRUNCATE TABLE #systriggers

INSERT INTO #systriggers

EXEC('USE [' + @DBName + '] SELECT * FROM [sys].[triggers]')

IF NOT EXISTS(SELECT * FROM #systriggers WHERE [parent_class_desc] = 'DATABASE' AND [name] = N'DDLChange_trg')

BEGIN

EXEC('

USE [' + @DBName + ']

GO

/*******************************************************************************************************

**Name:dbo.DDLChange_trg

**Desc:Trigger to capture all database DDL events

**Auth:Adam Bean (SQLSlayer.com)

Modified from: http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes

**Date:2008.09.24

*******************************************************************************

**Change History

*******************************************************************************

**Date:Author:Description:

**-------------------------------------------------------

********************************************************************************************************/

CREATE TRIGGER [DDLChange_trg] ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

SET NOCOUNT ON

DECLARE

@dataXML

,@ProgramNameVARCHAR(256)

SELECT @ProgramName = [program_name] FROM [sys].[dm_exec_sessions] WHERE [session_id] = @@SPID

SET @data = EVENTDATA()

INSERT INTO [' + @LoggingDBName + '].[dbo].[DDLChangeLog]

([DatabaseName], [EventType], [ObjectName], [ObjectType], [SqlCommand], [LoginName], [ProgramName])

VALUES

(

@data.value(''(/EVENT_INSTANCE/DatabaseName)[1]'', ''VARCHAR(256)'')

,@Data.value(''(/EVENT_INSTANCE/EventType)[1]'', ''VARCHAR(50)'')

,@Data.value(''(/EVENT_INSTANCE/ObjectName)[1]'', ''VARCHAR(256)'')

,@Data.value(''(/EVENT_INSTANCE/ObjectType)[1]'', ''VARCHAR(25)'')

,@Data.value(''(/EVENT_INSTANCE/TSQLCommand)[1]'', ''VARCHAR(MAX)'')

,@Data.value(''(/EVENT_INSTANCE/LoginName)[1]'', ''VARCHAR(256)'')

,@ProgramName

)

SET NOCOUNT OFF

')

END

ELSE

PRINT 'Trigger already exists in database: ' + @DBName + ''

FETCH NEXT FROM #dbs INTO @DBName

END

CLOSE #dbs

DEALLOCATE #dbs

SET NOCOUNT OFF

1
2

SELECT * INTO tmp_sprprn FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=D:\printer_stat.xlsx;Extended Properties=EXCEL 5.0')...[принтеры$];

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

CREATE TABLE ГОРОД
(
    ID_ГОРОДА INT IDENTITY(1,1) PRIMARY KEY,
    ГОРОД NVARCHAR(255) DEFAULT ' '
);
 
CREATE TABLE ТОВАР
(
    ID_ТОВАРА INT IDENTITY(1,1) PRIMARY KEY,
    ТОВАР NVARCHAR(255) DEFAULT ' '
);
 
CREATE TABLE ГРУЗЧИК
(
    ID_ГРУЗЧИКА INT IDENTITY(1,1) PRIMARY KEY,
    ГРУЗЧИК NVARCHAR(255) DEFAULT ' '
);
 
 
CREATE TABLE ТАБЛИЦА ДЛЯ ИМПОРТА
(
    ID_ЗАПИСИ INT IDENTITY(1,1) PRIMARY KEY,
    ФИО NVARCHAR(255) NOT NULL,
    ID_ГОРОДА INT DEFAULT ' ',
    ID_ТОВАРА INT DEFAULT ' ',
    ID_ГРУЗЧИКА INT DEFAULT ' ',
    ДАТА_ОТГРУЗКИ DATE DEFAULT ' ',
    FOREIGN KEY (ID_ГОРОДА) REFERENCES ГОРОД(ID_ГОРОДА),
    FOREIGN KEY (ID_ТОВАРА) REFERENCES ТОВАР(ID_ТОВАРА),
    FOREIGN KEY (ID_ГРУЗЧИКА) REFERENCES ГРУЗЧИК(ID_ГРУЗЧИКА)
)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

CREATE TABLE ИМПОРТ
(
ID_ЗАПИСИ INT IDENTITY(1,1) PRIMARY KEY,
ФИО NVARCHAR(255) NOT NULL,
ГОРОД NVARCHAR(255) DEFAULT ' ',
ТОВАР NVARCHAR(255) DEFAULT ' ',
ГРУЗЧИК NVARCHAR(255) DEFAULT ' '
ДАТА_ОТГРУЗКИ DATE DEFAULT ' '
)
 
 
 
 
CREATE VIEW v_ИМПОРТ
AS
SELECT     
  ИМПОРТ.ID_ЗАПИСИ, ИМПОРТ.ФИО, ГОРОД.ГОРОД AS ГОРОД, ТОВАР.ТОВАР AS ТОВАР, 
  ГРУЗЧИК.ГРУЗЧИК AS ГРУЗЧИК, ИМПОРТ.ДАТА
FROM       
  ИМПОРТ
  JOIN ГОРОД ON ИМПОРТ.ГОРОД = ГОРОД.ID_ГОРОДА 
  JOIN ТОВАР ON ИМПОРТ.ТОВАР = ТОВАР.ID_ТОВАРА
  JOIN ГРУЗЧИК ON ИМПОРТ.ГРУЗЧИК  = ГРУЗЧИК.ID_ГРУЗЧИКА  
  
 
CREATE TRIGGER Z_ИМПОРТ 
   ON v_ИМПОРТ
   INSTEAD OF INSERT
AS 
BEGIN
  SET NOCOUNT ON;
 
  INSERT INTO ИМПОРТ (ФИО, ГОРОД, ТОВАР, ГРУЗЧИК, ДАТА)     
  SELECT     
    inserted.ФИО, ГОРОД.ID_ГОРОДА, ТОВАР.ID_ТОВАРА, ГРУЗЧИК.ID_ГРУЗЧИКА , inserted.ДАТА
  FROM       
    inserted 
    JOIN ГОРОД ON inserted.ГОРОД = ГОРОД.ГОРОД
    JOIN ТОВАР ON inserted.ТОВАР = ТОВАР.ТОВАР
    JOIN ГРУЗЧИК ON inserted.ГРУЗЧИК  = ГРУЗЧИК.ГРУЗЧИК
END

1

GO

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE TRIGGER Z_ИМПОРТ 
ON ТАБЛИЦА ДЛЯ ИМПОРТА
INSTEAD OF INSERT
AS 
BEGIN
SET NOCOUNT ON;
 
INSERT INTO Z_ИМПОРТ (ФИО, ГОРОД, ТОВАР, ГРУЗЧИК, ДАТА) 
SELECT 
inserted.ФИО, ГОРОД.ID_ГОРОДА, ТОВАР.ID_ТОВАРА, ГРУЗЧИК.ID_ГРУЗЧИКА , inserted.ДАТА
FROM 
inserted 
JOIN ГОРОД ON inserted.ГОРОД = ГОРОД.ГОРОД
JOIN ТОВАР ON inserted.ТОВАР = ТОВАР.ТОВАР
JOIN ГРУЗЧИК ON inserted.ГРУЗЧИК = ГРУЗЧИК.ГРУЗЧИК
END

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE TRIGGER Z_ИМПОРТ 
ON ИМПОРТ
INSTEAD OF INSERT
AS 
BEGIN
SET NOCOUNT ON;
 
INSERT INTO [ТАБЛИЦА ДЛЯ ИМПОРТА] (ФИО, ID_ГОРОДА, ID_ТОВАРА, ID_ГРУЗЧИКА, ДАТА_ОТГРУЗКИ) 
SELECT 
inserted.ФИО, ГОРОД.ID_ГОРОДА, ТОВАР.ID_ТОВАРА, ГРУЗЧИК.ID_ГРУЗЧИКА , inserted.ДАТА_ОТГРУЗКИ
FROM 
inserted 
JOIN ГОРОД ON inserted.ГОРОД = ГОРОД.ГОРОД
JOIN ТОВАР ON inserted.ТОВАР = ТОВАР.ТОВАР
JOIN ГРУЗЧИК ON inserted.ГРУЗЧИК = ГРУЗЧИК.ГРУЗЧИК
END

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

CREATE TRIGGER Z_ИМПОРТ 
ON ИМПОРТ
INSTEAD OF INSERT
AS 
BEGIN
SET NOCOUNT ON;
 
INSERT INTO [ТАБЛИЦА ДЛЯ ИМПОРТА] (ФИО, ID_ГОРОДА, ID_ТОВАРА, ID_ГРУЗЧИКА, ДАТА_ОТГРУЗКИ) 
SELECT 
inserted.ФИО, ГОРОД.ID_ГОРОДА, ТОВАР.ID_ТОВАРА, ГРУЗЧИК.ID_ГРУЗЧИКА , inserted.ДАТА_ОТГРУЗКИ
FROM 
inserted 
JOIN ГОРОД ON inserted.ГОРОД = ГОРОД.ГОРОД
JOIN ТОВАР ON inserted.ТОВАР = ТОВАР.ТОВАР
JOIN ГРУЗЧИК ON inserted.ГРУЗЧИК = ГРУЗЧИК.ГРУЗЧИК
 
INSERT INTO [ИМПОРТ] (ФИО,  ГОРОД, ТОВАР, ГРУЗЧИК, ДАТА_ОТГРУЗКИ) 
SELECT 
inserted.ФИО, inserted.ГОРОД, inserted.ТОВАР, inserted.ГРУЗЧИК , inserted.ДАТА_ОТГРУЗКИ
FROM 
inserted 
LEFT JOIN ГОРОД ON inserted.ГОРОД = ГОРОД.ГОРОД
LEFT JOIN ТОВАР ON inserted.ТОВАР = ТОВАР.ТОВАР
LEFT JOIN ГРУЗЧИК ON inserted.ГРУЗЧИК = ГРУЗЧИК.ГРУЗЧИК
WHERE 
  ID_ГОРОДА IS NULL OR ID_ТОВАРА IS NULL OR ID_ГРУЗЧИКА IS NULL
 
END

Наверх

You just need to change the command delimiter that the client is looking for in order to split commands:

delimiter $$
CREATE TRIGGER new_trx_tmp_on_transaction_update AFTER UPDATE ON transaction
FOR EACH ROW
BEGIN
    IF NEW.trx_status = 1 AND ( NEW.cat_id = 'CASHBACK' || NEW.cat_id = 'REFUND') THEN
        INSERT INTO trx_tmp( `id`, `trx_id`, `user_id`, `trx_merchant_trxid`, `trx_amount`, `amount_left`, `cat_id`, `created_at`, `updated_at` ) VALUES ( NULL, NEW.id, NEW.userid, NEW.trx_merchant_trxid, NEW.trx_amount, NEW.trx_amount, NEW.cat_id, NOW(), NOW() );
    END IF;
END
$$

The default is a semi-colon, hence it complaining at the end of the INSERT line when you try and create your trigger.

It’s all explained in the documentation here.