I have the below trigger:
CREATE Trigger enroll_limit on Enrollments
Instead of Insert
As
Declare @Count int
Declare @Capacity int
Select @Count = COUNT(*) From Enrollments
Select @Capacity = Capacity From CourseSections
If @Count < @Capacity
Begin
Insert Into Enrollments Select * From Inserted
End
GO
I’m getting an error msg saying:
‘CREATE TRIGGER’ must be the first statement in a query batch.
Mitch Wheat
294k43 gold badges465 silver badges540 bronze badges
asked Nov 13, 2012 at 23:12
1
The error message «‘CREATE TRIGGER’ must be the first statement in a query batch.» usually occurs when a preceding group (batch) of statements does not have a terminating GO
So, I would suggest adding add a GO
to the end of the preceding batch’s statements.
answered Nov 14, 2012 at 4:55
Mitch WheatMitch Wheat
294k43 gold badges465 silver badges540 bronze badges
1
If you are trying this from SQL Server Management Studio, here is another option which worked for me:
In the left pane, right-click on the database and select «New Query».
This connects you to the specific database. Now you can enter your create trigger statement as the first statement in the query window which opens. There is no need for a «use» command.
answered Jun 17, 2019 at 18:55
jdhildebjdhildeb
3,2323 gold badges17 silver badges25 bronze badges
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).
в рамках некоторых административных задач у нас есть много таблиц, каждая из которых нуждается в создании триггера. Триггер установит флаг и дату в базе данных аудита, когда объект был изменен. Для простоты у меня есть таблица со всеми объектами, которые нужно создать триггеры.
Я пытаюсь создать динамический sql, чтобы сделать это для каждого объекта, но я получаю эту ошибку:'CREATE TRIGGER' must be the first statement in a query batch.
вот код для создания sql.
CREATE PROCEDURE [spCreateTableTriggers]
AS
BEGIN
DECLARE @dbname varchar(50),
@schemaname varchar(50),
@objname varchar(150),
@objtype varchar(150),
@sql nvarchar(max),
@CRLF varchar(2)
SET @CRLF = CHAR(13) + CHAR(10);
DECLARE ObjectCursor CURSOR FOR
SELECT DatabaseName,SchemaName,ObjectName
FROM Audit.dbo.ObjectUpdates;
SET NOCOUNT ON;
OPEN ObjectCursor ;
FETCH NEXT FROM ObjectCursor
INTO @dbname,@schemaname,@objname;
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = N'USE '+QUOTENAME(@dbname)+'; '
SET @sql = @sql + N'IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'''+QUOTENAME(@schemaname)+'.[Tiud_'+@objname+'_AuditObjectUpdates]'')) '
SET @sql = @sql + N'BEGIN DROP TRIGGER '+QUOTENAME(@schemaname)+'.[Tiud_'+@objname+'_AuditObjectUpdates]; END; '+@CRLF
SET @sql = @sql + N'CREATE TRIGGER '+QUOTENAME(@schemaname)+'.[Tiud_'+@objname+'_AuditObjectUpdates] '+@CRLF
SET @sql = @sql + N' ON '+QUOTENAME(@schemaname)+'.['+@objname+'] '+@CRLF
SET @sql = @sql + N' AFTER INSERT,DELETE,UPDATE'+@CRLF
SET @sql = @sql + N'AS '+@CRLF
SET @sql = @sql + N'IF EXISTS(SELECT * FROM Audit.dbo.ObjectUpdates WHERE DatabaseName = '''+@dbname+''' AND ObjectName = '''+@objname+''' AND RequiresUpdate=0'+@CRLF
SET @sql = @sql + N'BEGIN'+@CRLF
SET @sql = @sql + N' SET NOCOUNT ON;'+@CRLF
SET @sql = @sql + N' UPDATE Audit.dbo.ObjectUpdates'+@CRLF
SET @sql = @sql + N' SET RequiresUpdate = 1'+@CRLF
SET @sql = @sql + N' WHERE DatabaseName = '''+@dbname+''' '+@CRLF
SET @sql = @sql + N' AND ObjectName = '''+@objname+''' '+@CRLF
SET @sql = @sql + N'END' +@CRLF
SET @sql = @sql + N'ELSE' +@CRLF
SET @sql = @sql + N'BEGIN' +@CRLF
SET @sql = @sql + N' SET NOCOUNT ON;' +@CRLF
SET @sql = @sql + @CRLF
SET @sql = @sql + N' -- Update ''SourceLastUpdated'' date.'+@CRLF
SET @sql = @sql + N' UPDATE Audit.dbo.ObjectUpdates'+@CRLF
SET @sql = @sql + N' SET SourceLastUpdated = GETDATE() '+@CRLF
SET @sql = @sql + N' WHERE DatabaseName = '''+@dbname+''' '+@CRLF
SET @sql = @sql + N' AND ObjectName = '''+@objname+''' '+@CRLF
SET @sql = @sql + N'END; '+@CRLF
--PRINT(@sql);
EXEC sp_executesql @sql;
FETCH NEXT FROM ObjectCursor
INTO @dbname,@schemaname,@objname;
END
CLOSE ObjectCursor ;
DEALLOCATE ObjectCursor ;
END
если Я использую PRINT
и вставить код в новое окно запроса, код выполняется без каких-либо проблем.
Я удалил GO
заявления, поскольку это также давало ошибки.
что я упустил?
Почему я получаю ошибку, используя EXEC(@sql);
или даже EXEC sp_executesql @sql;
?
Это как-то связано с контекстом EXEC()
?
Большое спасибо за любую помощь.
2 ответов
если вы используете SSMS (или другой подобный инструмент) для запуска кода, созданного этой скрипт, вы получите точно такую же ошибку. Он может работать нормально, когда вы вставили разделители пакетов (GO
), но теперь, когда вы этого не сделаете, вы столкнетесь с той же проблемой в SSMS.
С другой стороны, причина, почему вы не можете поставить GO
в ваших динамических скриптах это потому, что GO
не является оператором SQL, это просто разделитель, распознанный SSMS и некоторыми другими инструментами. Возможно вы уже знаете об этом.
в любом случае, точки GO
для инструмента, чтобы знать, что код должен быть разделен и его части работать отдельно. И это,отдельно, это то, что вы должны сделать в своем коде.
Итак, у вас есть следующие варианты:
-
вставить
EXEC sp_execute @sql
сразу после части, которая сбрасывает триггер, затем сбросьте значение@sql
затем сохранить и запустить часть определения в его свою очередь; -
использовать две переменные,
@sql1
и@sql2
, сохранить если существует / падение часть в@sql1
создать триггер в@sql2
, затем запустить оба скрипта (опять же, отдельно).
но тогда, как вы уже выяснили, вы столкнетесь с другой проблемой: вы не можете создать триггер в другой базе данных без запуска оператора в контексте этой базы данных.
теперь, 2 пути обеспечивать необходимый контекст:
1) использовать USE
отчетность;
2) Запустите инструкцию(ы) как динамический запрос, используя EXEC targetdatabase..sp_executesql N'…'
.
очевидно, что первый вариант здесь не работает: мы не можем добавить USE …
до CREATE TRIGGER
, потому что последний должен быть единственным оператором в пакете.
второй вариант can используется, но для этого потребуется дополнительный слой динамичность (не уверен, что это слово). Это потому что имя базы данных является параметром здесь, и поэтому нам нужно запустить EXEC targetdatabase..sp_executesql N'…'
as динамический скрипт, и поскольку фактический скрипт для запуска сам по себе должен быть динамическим скриптом, он, следовательно, будет вложен дважды.
Итак, перед (второй) EXEC sp_executesql @sql;
строка добавить следующее:
SET @sql = N'EXEC ' + @dbname + '..sp_executesql N'''
+ REPLACE(@sql, '''', '''''') + '''';
как вы можете видеть, интегрировать содержание @sql
как вложенный динамический скрипт, они должны быть заключены в одинарные кавычки. По той же причине, каждый одинарная кавычка на @sql
должно быть удвоено (например, с помощью REPLACE()
функции, как и в приведенном выше утверждении).
Создание триггера должно выполняться в собственном пакете выполнения. Вы находитесь внутри процедуры, поэтому вы не сможете ее создать.
Я предлагаю добавить @sql в временную таблицу, а затем, как только proc закончит генерировать все операторы, зациклите эту временную таблицу, чтобы выполнить их и создать триггеры
Если вы используете SSMS (или другой аналогичный инструмент) для запуска кода, созданного этим script, вы получите точно такую же ошибку. При включении разделителей партий (GO
) он может работать нормально, но теперь, когда вы этого не сделаете, вы столкнетесь с той же проблемой в SSMS тоже.
С другой стороны, причина, по которой вы не можете поставить GO
в свои динамические сценарии, состоит в том, что GO
не является оператором SQL, а просто разделителем, распознанным SSMS и некоторыми другими инструментами. Возможно, вы уже знаете об этом.
В любом случае, точка GO
предназначена для того, чтобы инструмент знал, что код должен быть разделен, а его части выполняются отдельно. И это отдельно, это то, что вы должны делать и в своем коде.
Итак, у вас есть следующие опции:
вставьте EXEC sp_execute @sql
сразу после части, которая сбрасывает триггер, затем reset значение @sql
, чтобы затем сохранить и запустить часть определения в свою очередь;
используйте две переменные, @sql1
и @sql2
, сохраните часть IF EXISTS/DROP в @sql1
, CREATE TRIGGER один в @sql2
, затем запустите оба сценария (опять же, отдельно).
Но тогда, как вы уже выяснили, вы столкнетесь с другой проблемой: вы не можете создать триггер в другой базе данных без запуска инструкции в контексте этой базы данных.
Теперь существует два способа предоставления необходимого контекста:
1) используйте инструкцию USE
;
2) запустите оператор как динамический запрос, используя EXEC targetdatabase ..sp_executesql N'…'
.
Очевидно, что первый вариант не будет работать здесь: мы не можем добавить USE …
до CREATE TRIGGER
, потому что последнее должно быть единственным утверждением в пакете.
Можно использовать второй вариант, но для этого потребуется дополнительный уровень динамичности (не уверен, что это слово). Это потому, что здесь имя базы данных является параметром, поэтому нам нужно запустить EXEC targetdatabase ..sp_executesql N'…'
как динамический script, а так как фактический script для запуска сам должен быть динамическим script, он, следовательно, будет быть вложенными дважды.
Итак, перед строкой (второй) EXEC sp_executesql @sql;
добавьте следующее:
SET @sql = N'EXEC ' + @dbname + '..sp_executesql N'''
+ REPLACE(@sql, '''', '''''') + '''';
Как вы можете видеть, для правильного включения содержимого @sql
в качестве вложенного динамического script они должны быть заключены в одинарные кавычки. По той же причине каждая отдельная кавычка в @sql
должна быть удвоена (например, с помощью REPLACE()
function, как в приведенном выше заявлении).
Всякий раз, когда я запускаю это вместе с другими инструкциями SQL, он выдает ошибку:
CREATE FUNCTION ‘должен быть первым оператором в пакете запроса
Однако всякий раз, когда я выполняю его один (без SELECT * FROM CUSTOMER в моем случае это работает)
SELECT * FROM CUSTOMER;
create function udfLogin
(@username nvarchar(100), @password nvarchar(100))
returns int as
begin
if exists(select 1 from CUSTOMER where Username = @username and [Password] = @password)
return 1
return 0;
end
3 ответа
Лучший ответ
Вы должны отделить каждую партию кода с помощью ключевого слова GO.
SELECT * FROM CUSTOMER
GO
create function udfLogin
(@username nvarchar(100), @password nvarchar(100))
returns int as
begin
if exists(select 1 from CUSTOMER where Username = @username and
[Password] = @password)
return 1
return 0;
end
Go
1
Mehrnoosh
27 Мар 2018 в 11:27
Как следует из сообщения об ошибке, оператор CREATE FUNCTION должен быть первым оператором в пакете запроса. Перед оператором CREATION FUNCTION не должно быть других операторов, которые сделали бы его не первым оператором в пакете запроса. Как следует из сообщения, чтобы избежать этой ошибки, оператор CREATE FUNCTION должен быть первым оператором в пакете запроса.
SQL выполняет инструкцию как пакет запроса.
Правила использования пакетов
Следующие правила применяются к использованию пакетов:
Операторы CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER и CREATE VIEW нельзя объединять с другими операторами в пакете. Оператор CREATE должен запустить пакет. Все остальные операторы, которые следуют в этом пакете, будут интерпретированы как часть определения первого оператора CREATE.
0
Karthik Venkatraman
27 Мар 2018 в 11:37
Вы должны включить разделитель партии, например, Go
SELECT * FROM CUSTOMER
GO
create function udfLogin
(@username nvarchar(100), @password nvarchar(100))
returns int as
begin
if exists(select 1 from CUSTOMER where Username = @username and [Password] = @password)
return 1
return 0;
end
Go
2
Mazhar
27 Мар 2018 в 11:26
#sql #sql-server #view #ssms
#sql #sql-сервер #Вид #ssms
Вопрос:
Microsoft SQL Server Management Studio 18 показывает ошибку:
CREATE VIEW должна быть единственной инструкцией в пакете
После выполнения запроса появляется следующая ошибка:
Неправильный синтаксис вокруг ключевого слова «select»
create view revenue0 (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= '1996-05-01'
and l_shipdate < dateadd(mm,3,cast('1996-05-01' as datetime))
group by
l_suppkey;
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue0
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue0
)
order by
s_suppkey
option (maxdop 2)
drop view revenue0
UPD. Я попытался запустить этот метод:
create view revenue0 (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= cast('1996-05-01' as datetime)
and l_shipdate < dateadd(mm, 3, cast('1996-05-01' as datetime))
group by
l_suppkey;
go
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue0
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue0
)
order by
s_suppkey;
drop view revenue0;
Но в результате выполнения запроса отображается ошибка:
Недопустимое имя объекта «revenue0».
Как только я не изменил свое имя. SQL все равно ругается на это имя.
UPD2. Вопрос был решен самостоятельно. Тема закрыта! Спасибо всем за ваши усилия!
Комментарии:
1. Поместите слово
GO
после определения представления.2. Можете ли вы сказать мне, где именно? Я не очень хорошо разбираюсь в SQL. Я буду очень благодарен!
3.
group by l_suppkey; GO
4. Неправильный синтаксис вокруг конструкции «go».
5. вы не можете использовать «order by» в представлении, измените свой вид, чтобы не использовать его.
Ответ №1:
Ошибка сообщает, что CREATE VIEW должен быть единственным оператором в пакете. Пакет завершается в SQL Server ключевым словом «GO», как заявил Гордон, поэтому ваш код должен выглядеть следующим образом:
create view revenue0 (supplier_no, total_revenue) as
select
l_suppkey,
sum(l_extendedprice * (1 - l_discount))
from
lineitem
where
l_shipdate >= '1996-05-01'
and l_shipdate < dateadd(mm,3,cast('1996-05-01' as datetime))
group by
l_suppkey;
GO -- right here. This ends a batch. Must be on a new line, with no semi-color, or SQL gets pissy.
select
s_suppkey,
s_name,
s_address,
s_phone,
total_revenue
from
supplier,
revenue0
where
s_suppkey = supplier_no
and total_revenue = (
select
max(total_revenue)
from
revenue0
)
order by
s_suppkey
option (maxdop 2);
drop view revenue0;
Комментарии:
1. Я тестирую «Тесты TPC-H на MS-SQL-Server» этот материал: ссылка . При выполнении этого запроса это должно быть: link .
2. Но у меня, с вашей поправкой, после выполнения SQL-запроса выводится пустая таблица. Время выполнения запроса составляет 0 секунд. Это ненормально.
3. Вы можете помочь переделать этот запрос, чтобы он работал: ссылка .
4. Я уже всю голову сломал с ним. Но этот конкретный запрос не хочет работать. Все остальные работают.
Вопрос:
Предположим, что у меня есть таблицы T1 и T2
Columns of T1 -->Value
Columns of T2 -->OldValue NewValue
Что мне нужно – это триггер, который будет вставлять запись в T2 при обновлении T1, мне также нужно знать старое значение и новое значение, я никогда не использовал триггеры раньше, так что может ли кто-нибудь помочь мне в этом, как сделать Я собираюсь создать этот триггер. Возможно, спасибо.
Лучший ответ:
Ну, вы начинаете писать триггер с CREATE TRIGGER
:
CREATE TRIGGER NameOfTheTriggerPlease
…
Таблица, которая должна инициировать дополнительное действие, равна T1
, поэтому триггер должен быть определен ON
в этой таблице:
CREATE TRIGGER T1OnUpdate /* that just an example,
you can use a different name */
ON T1
…
Действие, на которое должен запускаться триггер, – UPDATE
, а время AFTER
– обновление, поэтому…
CREATE TRIGGER T1OnUpdate
ON T1
AFTER UPDATE
…
Теперь пришло время ввести тело триггера, т.е. инструкции, которые должны быть выполнены триггером. Вы вводите тело с ключевым словом AS
, за которым следуют сами утверждения.
В вашем случае будет только одно утверждение INSERT
, что очевидно. Что не так очевидно, так это то, как мы будем обращаться к старым и новым значениям. Теперь SQL Server предлагает вам две виртуальные таблицы INSERTED
и DELETED
, и вы можете легко предположить, что первая содержит все новые значения, а последние – старые.
Эти таблицы имеют ту же структуру, что и таблица, которой назначен триггер, т.е. T1
. Они содержат только строки, на которые повлиял конкретный оператор UPDATE
, который вызывал триггер, что означает, что их может быть несколько. А это, в свою очередь, означает, что вам нужно иметь в своей таблице T1
первичный ключ или уникальный столбец (или набор столбцов), который можно использовать в триггере для соответствия удаленным и вставленным строкам. (На самом деле вам может понадобиться таблица T2
, чтобы иметь столбец, который ссылался бы на первичный ключ T1
, поэтому вы могли бы позже установить, какая строка T1
имела значения, хранящиеся в T2
.)
В целях этого ответа я собираюсь предположить, что в столбце первичного ключа с именем PK
и в столбце внешнего ключа с тем же именем в T2
. А инструкция INSERT
может выглядеть так:
CREATE TRIGGER T1OnUpdate
ON T1
AFTER UPDATE
AS
INSERT INTO T2 (PK, OldValue, NewValue)
SELECT i.PK, i.Value, d.Value
FROM INSERTED i INNER JOIN DELETED d ON i.PK = d.PK
Последнее, но не менее важное: помните: весь оператор CREATE TRIGGER
должен быть единственным в пакете, т.е. не должно быть операторов, предшествующих ключевым словам CREATE TRIGGER
(но вы можете размещать там комментарии) и аналогично, все после ключевого слова AS
считается частью тела триггера (но вы можете поместить разделитель GO
, чтобы указать конец инструкции, если вы используете script в SQL Server Management Studio, например).
Полезное чтение:
- CREATE TRIGGER (Transact-SQL)
Ответ №1
Я не собираюсь строить для вас все это (весело, правда?), но я могу указать вам в правильном направлении
create trigger logUpdate
on T1
After update
as
begin
insert into T2...
--here is just an example
select * from deleted --the DELETED table contains the OLD values
select * from inserted --the INSERTED table contains the NEW values
end
помните, что DELETED и INSERTED – это внутренние таблицы, содержащие старые и новые значения. В триггере обновления они оба существуют. В триггере вставки DELETED будет пустым, поскольку ничего не будет удалено. Такая же логика в триггере удаления, INSERTED будет пустым
EDIT:
Отвечая на ваш вопрос: независимо от того, сколько полей вы обновляете, ваши таблицы DELETED и INSERTED имеют все столбцы всех затронутых строк. Конечно, если вы обновите только один столбец, все остальные будут иметь одинаковое значение на DELETED и INSERTED
Ответ №2
create trigger T_UPD_T1
on T1 FOR update
as
insert into T2 select deleted.value, inserted.value from inserted, deleted