Контроль UPDATE триггер. Доступ к данным, которые в запросе

@Learx · Регистрация: 31.03.2010

Author24 — интернет-сервис помощи студентам

При обновлении некоторых данных требуется отдельное подтверждение.

То есть при одинаковом запросе надо или обновить данные или запретить их.
Как это реализовать одним и тем же триггером не догадываюсь.

Создал таблицу для временного хранения данных и представление на обновляемую таблицу.
В случае обновления без подтверждение отправляю обновление на представление и триггером Instead of update хочу сохранить данные во временную таблицу(с подтверждением беру данные со временной таблицы и напрямую отправляю в изменяемую таблицу). Однако изменений в представлении нет, потому в таблицах inserted и deleted не попадают новые данные.

Как получить эти новые данные?

или есть другой способ реализовать поставленную задачу?

@invm · 25.06.2013, 15:56

Если правильно понял, что требуется:

T-SQL

create table dbo.Data (id int not null primary key, Value int);
create table dbo.Data_QueuedForApprove (id int not null primary key, Value int);
go
 
create view dbo.vData
as
select
 d.id,
 isnull(q.Value, d.Value) as Value,
 cast(case when q.id is not null then 1 else 0 end) as ShouldBeApproved
from
 dbo.Data d left join
 dbo.Data_QueuedForApprove q on q.id = d.id
go
 
create trigger dbo.trvData_iou
on dbo.vData
instead of update
begin
 set nocount on;
 
 if not exists(select 1 from inserted)
  return;
 
 update d
  set
   Value = i.Value
 from
  inserted i join
  dbo.Data d on d.id = i.id
 where
  i.ShoupdBeApproved = 0;
 
 with s as
 (
  select
  from
   inserted
  where
   ShoupdBeApproved = 1
 )
 merge into dbo.Data_QueuedForApprove t
 using s on s.id = t.id
 when matched then
  update set Value = s.Value
 when not matched then
  insert (id, Value) values (s.id, s.Value);
end;
go
 
create procedure dbo.ApproveData
as
begin
 set nocount on;
 
 update d
  set
   Value = q.Value
 from
  dbo.Data_QueuedForApprove q 
  dbo.Data d on d.id = q.id;
 
 delete from dbo.Data_QueuedForApprove;
end;
go

Вызов dbo.ApproveData естественно надо заворачивать в транзакцию.

@cygapb-007 · 25.06.2013, 17:25

я думаю, предполагалось нечто иное
вроде вот этого:

T-SQL

if OBJECT_ID('dbo.Data','U') is not null drop table dbo.Data
if OBJECT_ID('dbo.Data_QueuedForApprove','U') is not null drop table dbo.Data_QueuedForApprove
if OBJECT_ID('TR_Data_IOU', 'TR') is not null drop trigger TR_Data_IOU
GO
create table dbo.Data (id int not null primary key, Value int);
create table dbo.Data_QueuedForApprove (id int not null primary key, Value int);
GO
create trigger TR_Data_IOU on dbo.Data
instead of update
as
set nocount on
update d set d.Value=q.Value
   from inserted i 
   join dbo.Data_QueuedForApprove q on q.id=i.id and q.Value=i.Value
   join dbo.Data d on d.id=q.id;
merge dbo.Data_QueuedForApprove q
   using inserted i on i.id=q.id
   when matched and i.value=q.value then
      delete
   when matched then
      update set q.value=i.value
   when not matched by target then
      insert (id,value) values(i.id, i.value);
GO

T-SQL

-- начальное состояние
insert dbo.Data values(1,10), (2,20), (3,30)
select * from dbo.Data
select * from dbo.Data_QueuedForApprove
 
-- изменение одной позиции
update dbo.Data set Value=100 where id=1
select * from dbo.Data
select * from dbo.Data_QueuedForApprove
-- подтверждение изменения одной позиции
update dbo.Data set Value=100 where id=1
select * from dbo.Data
select * from dbo.Data_QueuedForApprove
 
-- изменение нескольких позиций
update dbo.Data set Value+=5 where id>1
select * from dbo.Data
select * from dbo.Data_QueuedForApprove
-- подтверждение изменения нескольких позиций
update dbo.Data set Value+=5 where id>1
select * from dbo.Data
select * from dbo.Data_QueuedForApprove
 
-- изменение одной позиции
update dbo.Data set Value=400 where id=3
select * from dbo.Data
select * from dbo.Data_QueuedForApprove
-- корректировка ошибочного изменения одной позиции
update dbo.Data set Value=300 where id=3
select * from dbo.Data
select * from dbo.Data_QueuedForApprove
-- подтверждение корректировки одной позиции
update dbo.Data set Value=300 where id=3
select * from dbo.Data
select * from dbo.Data_QueuedForApprove
 
GO
drop trigger TR_Data_IOU
drop table dbo.Data, dbo.Data_QueuedForApprove

@Learx · 25.06.2013, 17:41 **[ТС]**

в представлении простая выборка из целевой таблицы.

T-SQL

CREATE TRIGGER [dbo].UpdatePrice
   ON  [dbo].[ArtiklView]
   INSTEAD OF UPDATE
AS 
BEGIN
    IF(UPDATE(PriceVal)) --проверяем обновляется ли нужный столбец
    begin
        --если да вместо обновления сохраняем новые значения из запроса
        --и тут сохраняются вдруг не новые, а старые данные!
    INSERT INTO [Masterplast-denis].[dbo].[ArtiklTmpPrice]
           ([ArtNo]
           ,[ClrNo]
           ,[CurVal]
           ,[Price]
           ,[Day])
     Select ArtNo, ClrNo, [CurVal], Price, DayPrice from inserted
    end
--обновляем все остальные поля целевой таблицы
    Update Artikl 
    set Artikl.TypeId=Ins.TypeId, Artikl.SubType=Ins.SubType, Artikl.Actual=Ins.Actual, Artikl.IsBar=Ins.IsBar, Artikl.[Reverse]=Ins.[Reverse], Artikl.[Weight]=Ins.[Weight] 
    from inserted as Ins
    where Artikl.ArtNo=Ins.ArtNo and Artikl.ClrNo=Ins.ClrNo
END

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

Добавлено через 10 минут
@cygapb-007, дело в том, что при выполнении merge в inserted Хранятся не те значения, которые необходимо сохранить!

@cygapb-007 · 25.06.2013, 17:56

Сообщение от Learx

@cygapb-007, дело в том, что при выполнении merge в inserted Хранятся не те значения, которые необходимо сохранить!

По шагам:
1. Исполнитель выполняет

T-SQL

1	update dbo.Data set Value=100 where id=1

В очереди появляется строка (1, 100), исходная таблица не изменяется
2. Контролер видит, что в очереди появилось новая строка (1,100), причем видит через VIEW в виде (1, 10, 100)
3.а. Контролер одобряет изменение и выполняет

T-SQL

1	update dbo.Data set Value=100 where id=1

в результате строка (1,100) из очереди уходит, а в таблицу вносится одобренное значение 100
3.б. Контролер отклоняет изменение и выполняет

T-SQL

1	delete dbo.Data_QueuedForApprove where id=1

Изменение уходит из очереди, исходная таблица не изменена.

Что не так?

Добавлено через 4 минуты
да, еще вариант забыл:
3.в. Контролер изменяет неверное значение update dbo.Data set Value=150 where id=1 и видит, что данные в очереди изменились с (1,10, 100) на (1,10,150)
Новое значение его устраивает, и он подтверждает корректировку, выполнив еще раз update dbo.Data set Value=150 where id=1

@invm · 25.06.2013, 18:30

Сообщение от Learx

дело в том, что при выполнении merge в inserted Хранятся не те значения, которые необходимо сохранить!

В inserted находятся ровно те значения, которые сформированы инструкцией, приведшей к срабатыванию триггера.
В вашем случае не нужно никаких приседаний с вью и триггерами. Надо иметь две таблицы: "Макет цен" и "Действующие цены". Вся работа по изменению цен ведется напрямую с таблицей "Макет цен". В "Действующие цены" данные попадают только после утверждения уполномоченным лицом позиций из "Макет цен". Это гораздо проще и легче сопровождаемо, чем попытки разрулить все возможные коллизии через вью с триггером.

Добавлено через 2 минуты
@cygapb-007, что произойдет, если Исполнитель дважды выполняет

T-SQL

1	update dbo.Data set Value=100 where id=1

@cygapb-007 · 25.06.2013, 19:16

Сообщение от invm

@cygapb-007, что произойдет, если Исполнитель дважды выполняет

T-SQL

1	update dbo.Data set Value=100 where id=1

если интерфейс ему это позволит, то будет считаться, что второй раз он контролер(

ЯТД, либо user_id задействовать дополнительно, либо в интерфейсе исполнителя

T-SQL

1
2
3

select d.id, ISNULL(q.Value, d.Value)
from dbo.Data d
left join dbo.Data_QueuedForApprove q on q.id=d.id

тогда он будет видеть "свое" значение 100 (хотя в таблице хранится 10), и не сможет изменить 100 на 100 повторно...

Добавлено через 2 минуты
зато плюс - если двое исполнителей внесли свои изменения по одной и той же позиции - в очереди будет зафиксировано только последнее...

Добавлено через 7 минут
еще как вариант - create table dbo.Data_QueuedForApprove (id int not null primary key, Value int, isChecked bit); с соответствующей обвязкой в триггере

Добавлено через 29 минут
как вариант, с учетом разных интерфейсов Исполнителя и Контролера:

T-SQL

if OBJECT_ID('TR_Data_IOU', 'TR') is not null drop trigger TR_Data_IOU
if OBJECT_ID('TR_Data_QueuedForApprove_AU', 'TR') is not null drop trigger TR_Data_QueuedForApprove_AU
if OBJECT_ID('dbo.Data','U') is not null drop table dbo.Data
if OBJECT_ID('dbo.Data_QueuedForApprove','U') is not null drop table dbo.Data_QueuedForApprove
GO
create table dbo.Data (id int not null primary key, Value int);
create table dbo.Data_QueuedForApprove (id int not null primary key, Value int, isChecked bit);
GO
create trigger TR_Data_IOU on dbo.Data
instead of update
as
set nocount on
update d set d.Value=q.Value
   from inserted i 
   join dbo.Data_QueuedForApprove q on q.id=i.id and q.isChecked=1
   join dbo.Data d on d.id=q.id;
merge dbo.Data_QueuedForApprove q
   using inserted i on i.id=q.id
   when matched and q.isChecked=1 then
      delete
   when not matched by target then
      insert (id,value) values(i.id, i.value);
GO
create trigger TR_Data_QueuedForApprove_AU on dbo.Data_QueuedForApprove 
after update
as
set nocount on
update d set d.Value=i.Value
   from inserted i
   join dbo.Data d on d.id=i.id
   where i.isChecked=1
GO

Кликните здесь для просмотра всего текста

T-SQL

-- начальное состояние
insert dbo.Data values(1,10), (2,20), (3,30)
select '0. исполнитель' [исполнитель], d.id, ISNULL(q.Value, d.Value)Value, d.Value [(просмотр realValue)] from dbo.Data d left join dbo.Data_QueuedForApprove q on q.id=d.id
select '0. контролер' [контролер], q.id, q.Value newValue, d.Value oldValue, ISNULL(q.isChecked,0) isChecked from dbo.Data_QueuedForApprove q join dbo.Data d on d.id=q.id
 
-- изменение одной позиции
update dbo.Data set Value=100 where id=1
select '1. исполнитель' [исполнитель], d.id, ISNULL(q.Value, d.Value)Value, d.Value [(просмотр realValue)] from dbo.Data d left join dbo.Data_QueuedForApprove q on q.id=d.id
select '1. контролер' [контролер], q.id, q.Value newValue, d.Value oldValue, ISNULL(q.isChecked,0) isChecked from dbo.Data_QueuedForApprove q join dbo.Data d on d.id=q.id
-- подтверждение изменения одной позиции
update dbo.Data_QueuedForApprove set isChecked=1 where id=1
select '1. исполнитель' [исполнитель], d.id, ISNULL(q.Value, d.Value)Value, d.Value [(просмотр realValue)] from dbo.Data d left join dbo.Data_QueuedForApprove q on q.id=d.id
select '1. контролер' [контролер], q.id, q.Value newValue, d.Value oldValue, ISNULL(q.isChecked,0) isChecked from dbo.Data_QueuedForApprove q join dbo.Data d on d.id=q.id
 
-- изменение нескольких позиций
update dbo.Data set Value+=5 where id>1
select '2. исполнитель' [исполнитель], d.id, ISNULL(q.Value, d.Value)Value, d.Value [(просмотр realValue)] from dbo.Data d left join dbo.Data_QueuedForApprove q on q.id=d.id
select '2. контролер' [контролер], q.id, q.Value newValue, d.Value oldValue, ISNULL(q.isChecked,0) isChecked from dbo.Data_QueuedForApprove q join dbo.Data d on d.id=q.id
-- подтверждение изменения нескольких позиций
update dbo.Data_QueuedForApprove set isChecked=1 where id>1
select '2. исполнитель' [исполнитель], d.id, ISNULL(q.Value, d.Value)Value, d.Value [(просмотр realValue)] from dbo.Data d left join dbo.Data_QueuedForApprove q on q.id=d.id
select '2. контролер' [контролер], q.id, q.Value newValue, d.Value oldValue, ISNULL(q.isChecked,0) isChecked from dbo.Data_QueuedForApprove q join dbo.Data d on d.id=q.id
 
-- изменение одной позиции
update dbo.Data set Value=350 where id=3
select '3. исполнитель' [исполнитель], d.id, ISNULL(q.Value, d.Value)Value, d.Value [(просмотр realValue)] from dbo.Data d left join dbo.Data_QueuedForApprove q on q.id=d.id
select '3. контролер' [контролер], q.id, q.Value newValue, d.Value oldValue, ISNULL(q.isChecked,0) isChecked from dbo.Data_QueuedForApprove q join dbo.Data d on d.id=q.id
-- изменение той же позиции (другим исполнителем?)
update dbo.Data set Value=450 where id=3
select '3. исполнитель' [исполнитель], d.id, ISNULL(q.Value, d.Value)Value, d.Value [(просмотр realValue)] from dbo.Data d left join dbo.Data_QueuedForApprove q on q.id=d.id
select '3. контролер' [контролер], q.id, q.Value newValue, d.Value oldValue, ISNULL(q.isChecked,0) isChecked from dbo.Data_QueuedForApprove q join dbo.Data d on d.id=q.id
-- утверждение правильной суммы контролером
update dbo.Data_QueuedForApprove set Value=300, isChecked=1 where id=3
select '3. исполнитель' [исполнитель], d.id, ISNULL(q.Value, d.Value)Value, d.Value [(просмотр realValue)] from dbo.Data d left join dbo.Data_QueuedForApprove q on q.id=d.id
select '3. контролер' [контролер], q.id, q.Value newValue, d.Value oldValue, ISNULL(q.isChecked,0) isChecked from dbo.Data_QueuedForApprove q join dbo.Data d on d.id=q.id
 
GO
drop trigger TR_Data_IOU, TR_Data_QueuedForApprove_AU
drop table dbo.Data, dbo.Data_QueuedForApprove

@invm · 25.06.2013, 19:18

Сообщение от cygapb-007

еще как вариант - create table dbo.Data_QueuedForApprove (id int not null primary key, Value int, isChecked bit); с соответствующей обвязкой в триггере

И получится практически тоже самое, что в моем первом ответе

@cygapb-007 · 25.06.2013, 19:34

с программным запретом повторной корректировки исполнителем - первый вариант проще)
с разными интерфейсами... наверное все же не совсем то же самое получилось... хотя да, похоже

Добавлено через 3 минуты
и в последнем варианте исправить новое значение нельзя, пока не будет очищена очередь, что может быть несколько неудобным, но - с внесением исправлений возникает недопустимая рекурсия в триггерах

@Learx 1057 / 864 / 195 Регистрация: 31.03.2010 Сообщений: 2,521
		1
	Контроль UPDATE триггер. Доступ к данным, которые в запросе 25.06.2013, 15:07. Показов 1703. Ответов 8 Метки нет (Все метки) При обновлении некоторых данных требуется отдельное подтверждение. То есть при одинаковом запросе надо или обновить данные или запретить их. Как это реализовать одним и тем же триггером не догадываюсь. Создал таблицу для временного хранения данных и представление на обновляемую таблицу. В случае обновления без подтверждение отправляю обновление на представление и триггером Instead of update хочу сохранить данные во временную таблицу(с подтверждением беру данные со временной таблицы и напрямую отправляю в изменяемую таблицу). Однако изменений в представлении нет, потому в таблицах inserted и deleted не попадают новые данные. Как получить эти новые данные? или есть другой способ реализовать поставленную задачу? 0

@invm 3499 / 2083 / 742 Регистрация: 02.06.2013 Сообщений: 5,078
	25.06.2013, 19:18	8
	Сообщение от cygapb-007 еще как вариант - create table dbo.Data_QueuedForApprove (id int not null primary key, Value int, isChecked bit); с соответствующей обвязкой в триггере И получится практически тоже самое, что в моем первом ответе 0

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	25.06.2013, 19:34	9
	с программным запретом повторной корректировки исполнителем - первый вариант проще) с разными интерфейсами... наверное все же не совсем то же самое получилось... хотя да, похоже Добавлено через 3 минуты и в последнем варианте исправить новое значение нельзя, пока не будет очищена очередь, что может быть несколько неудобным, но - с внесением исправлений возникает недопустимая рекурсия в триггерах 0