Синтаксис SQL Server "ПОСЛЕ ВСТАВКИ" не видит строку с вставкой

Ответ 1

Триггеры не могут изменять измененные данные (Inserted или Deleted), иначе вы можете получить бесконечную рекурсию, так как изменения снова вызвали триггер. Один из вариантов заключается в том, чтобы триггер отменил транзакцию.

Изменить: Причина этого в том, что стандартом для SQL является то, что вставленные и удаленные строки не могут быть изменены с помощью триггера. Основная причина заключается в том, что модификации могут привести к бесконечной рекурсии. В общем случае эта оценка может включать несколько триггеров во взаимно-рекурсивном каскаде. Наличие системы разумно решает, разрешать ли такие обновления вычислительно трудноразрешимую, по существу вариацию проблемы .

Принятое решение заключается не в том, чтобы позволить триггеру изменять изменяющиеся данные, хотя он может откатить транзакцию.

create table Foo (
       FooID int
      ,SomeField varchar (10)
)
go

create trigger FooInsert
    on Foo after insert as
    begin
        delete inserted
         where isnumeric (SomeField) = 1
    end
go


Msg 286, Level 16, State 1, Procedure FooInsert, Line 5
The logical tables INSERTED and DELETED cannot be updated.

Что-то вроде этого откатит транзакцию.

create table Foo (
       FooID int
      ,SomeField varchar (10)
)
go

create trigger FooInsert
    on Foo for insert as
    if exists (
       select 1
         from inserted 
        where isnumeric (SomeField) = 1) begin
              rollback transaction
    end
go

insert Foo values (1, '1')

Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Ответ 2

Вы можете отменить логику. Вместо того, чтобы удалить недопустимую строку после ее вставки, напишите триггер INSTEAD OF, чтобы вставить, только если вы подтвердите правильность строки.

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  DECLARE @isnum TINYINT;

  SELECT @isnum = ISNUMERIC(somefield) FROM inserted;

  IF (@isnum = 1)
    INSERT INTO sometable SELECT * FROM inserted;
  ELSE
    RAISERROR('somefield must be numeric', 16, 1)
      WITH SETERROR;
END

Если ваше приложение не хочет обрабатывать ошибки (как говорит Джоэл в своем приложении), тогда не RAISERROR. Просто сделайте триггер молча, не вставляя вставку, которая недействительна.

Я запускал это на SQL Server Express 2005, и он работает. Обратите внимание, что триггеры INSTEAD OF не вызывают рекурсии, если вы вставляете в ту же таблицу, для которой определен триггер.

Ответ 3

Здесь моя измененная версия кода Билла:

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  INSERT INTO sometable SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 1 FROM inserted;
  INSERT INTO sometableRejects SELECT * FROM inserted WHERE ISNUMERIC(somefield) = 0 FROM inserted;
END

Это позволяет вставить всегда успешное выполнение, и любые фиктивные записи попадают в ваши sometableRejects, где вы можете обращаться с ними позже. Важно, чтобы таблица rejects использовала поля nvarchar для всего: не ints, tinyints и т.д., Потому что, если они отклоняются, это потому, что данные не так, как вы ожидали.

Это также решает проблему с вставкой нескольких записей, что приведет к сбою триггера Bill. Если вы вставляете десять записей одновременно (например, если вы делаете select-insert-into), и только один из них фиктивный, триггер Bill будет отмечать все их как плохие. Это обрабатывает любое количество хороших и плохих записей.

Я использовал этот трюк в проекте хранилища данных, где приложение-вставка не знала, была ли бизнес-логика хорошей, и вместо этого мы использовали бизнес-логику в триггерах. Поистине противный для производительности, но если вы не можете позволить вставить сбой, он работает.

Ответ 4

Я думаю, вы можете использовать ограничение CHECK - это именно то, для чего он был изобретен.

ALTER TABLE someTable 
ADD CONSTRAINT someField_check CHECK (ISNUMERIC(someField) = 1) ;

Мой предыдущий ответ (также верно, может быть немного перебор):

Я думаю, что правильным способом является использование триггера INSTEAD OF для предотвращения ввода неправильных данных (вместо удаления его после факта)

Ответ 5

UPDATE: DELETE из триггера работает как на MSSql 7, так и на MSSql 2008.

Я не являюсь реляционным гуру, ни стандартным стандартом SQL. Однако - вопреки принятому ответу - MSSQL отлично справляется с r эксурсивной и вложенной оценкой триггеров. Я не знаю о других РСУБД.

Соответствующими параметрами являются "рекурсивные триггеры" и "вложенные триггеры" . Вложенные триггеры ограничены 32 уровнями и по умолчанию 1. Рекурсивные триггеры отключены по умолчанию, и нет никаких разговоров о лимите, но, честно говоря, я их никогда не включал, поэтому я не знаю, что происходит с неизбежным переполнение стека. Я подозреваю, что MSSQL просто убьет ваш spid (или есть рекурсивный предел).

Конечно, это просто показывает, что принятый ответ имеет неправильную причину, а не то, что он неверен. Тем не менее, до триггеров INSTEAD OF, я вспоминаю запись триггеров INSERT, которые бы весело UPDATE только что вставили строки. Все это работало нормально и, как ожидалось.

Быстрая проверка DELETEing только что вставленной строки также работает:

 CREATE TABLE Test ( Id int IDENTITY(1,1), Column1 varchar(10) )
 GO

 CREATE TRIGGER trTest ON Test 
 FOR INSERT 
 AS
    SET NOCOUNT ON
    DELETE FROM Test WHERE Column1 = 'ABCDEF'
 GO

 INSERT INTO Test (Column1) VALUES ('ABCDEF')
 --SCOPE_IDENTITY() should be the same, but doesn't exist in SQL 7
 PRINT @@IDENTITY --Will print 1. Run it again, and it'll print 2, 3, etc.
 GO

 SELECT * FROM Test --No rows
 GO

У вас здесь что-то еще.

Ответ 6

В документации CREATE TRIGGER:

удаленные и вставлены являются логическими (концептуальными) таблицами. Они есть структурно подобный таблице который определяется триггером, то есть, таблица, на которой выполняется действие пользователя пытались и сохраняли старые значения или новые значения строк, которые могут быть измененный действием пользователя. Для Например, чтобы получить все значения в удаленная таблица, используйте: SELECT * FROM deleted

Таким образом, по крайней мере, дает вам возможность увидеть новые данные.

Я не вижу ничего в документах, которые указывают, что вы не увидите вставленные данные при запросе обычной таблицы, хотя...

Ответ 7

Я нашел эту ссылку:

create trigger myTrigger
on SomeTable
for insert 
as 
if (select count(*) 
    from SomeTable, inserted 
    where IsNumeric(SomeField) = 1) <> 0
/* Cancel the insert and print a message.*/
  begin
    rollback transaction 
    print "You can't do that!"  
  end  
/* Otherwise, allow it. */
else
  print "Added successfully."

Я не тестировал его, но логически, похоже, что он должен делать то, что вам нужно... вместо того, чтобы удалять вставленные данные, не допускать вставки полностью, поэтому вам не нужно отменить вставку. Он должен работать лучше, и поэтому он должен в конечном итоге справляться с большей нагрузкой с большей легкостью.

Edit: Конечно, существует вероятность того, что если вставка произошла внутри другой действительной транзакции, которую можно было бы отбросить транзакцию с волосами, поэтому вам нужно будет учитывать этот сценарий и определить, если вставка неверных данных строка будет представлять собой полностью недействительную транзакцию...

Ответ 8

Методы, описанные выше, очень хорошо описывают ваши варианты. Но что видят пользователи? Я не могу себе представить, как основной конфликт, подобный этому, между вами и тем, кто несет ответственность за программное обеспечение, не может закончиться путаницей и антагонизмом с пользователями.

Я сделал бы все, что мог, чтобы найти какой-то другой выход из тупика, потому что другие люди могли легко увидеть любые изменения, которые вы вносите в эскалацию проблемы.

EDIT:

Я заработаю свой первый "восстановить" и допущу, чтобы опубликовать выше, когда этот вопрос впервые появился. Я, конечно, выкрикнул, когда увидел, что это от Джола Спольского. Но похоже, что он приземлился где-то рядом. Не нужны голоса, но я поставлю их на запись.

IME, триггеры редко являются правильным ответом на все, кроме мелкомасштабных ограничений целостности вне сферы бизнес-правил.

Ответ 9

Возможно ли, что INSERT является допустимым, но после этого выполняется отдельное UPDATE, которое является недопустимым, но не запускает триггер?

Ответ 10

MS-SQL имеет настройку для предотвращения рекурсивного запуска триггера. Это защищено с помощью хранимой процедуры sp_configure, в которую вы можете включить или отключить рекурсивные или вложенные триггеры.

В этом случае было бы возможно, если вы отключите рекурсивные триггеры, чтобы связать запись из вставленной таблицы с помощью первичного ключа и внести изменения в запись.

В конкретном случае в вопросе это не проблема, потому что результат заключается в том, чтобы удалить запись, которая не будет восстанавливать этот конкретный триггер, но в целом это может быть действительный подход. Мы реализовали оптимистичный concurrency таким образом.

Код для вашего триггера, который можно использовать таким образом, будет следующим:

ALTER TRIGGER myTrigger
    ON someTable
    AFTER INSERT
AS BEGIN
DELETE FROM someTable
    INNER JOIN inserted on inserted.primarykey = someTable.primarykey
    WHERE ISNUMERIC(inserted.someField) = 1
END