Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
 не удается написать триггер  [new]
dante0101
Member

Откуда:
Сообщений: 1
Добрый вечер!
Столкнулся я с одной проблемой, не знаю как решить
Суть задачи такова. При добавлении записи в таблицу процедура,из таблицы расход должны браться количество расходных материалов и отниматься от таблицы расходные материалы
Написанный мною триггер выдает: Вложенный запрос вернул больше одного значения.



create database ANALIZ2
go 
use ANALIZ2
 go 
 create table Работник
 (
 ID_РАБОТНИК tinyint identity(1,1) not null primary key,
 Фамилия varchar (50) not null,
 Имя varchar (50) not null,
 Отчество varchar (50) not null,
 Номер_т int  not null CONSTRAINT SUBJ_LIMIT CHECK(Номер_т>=1000000 AND Номер_т<=9999999),
 Должность varchar (50) not null
 )
 create table Пациент
 (
 ID_Пациента tinyint identity(1,1) not null primary key,
 Фамилия varchar (50) not null,
 Имя varchar (50) not null,
 Отчество varchar (50) not null,
 Номер_т int  not null,
 Дата_Рождения date not null
 )
 create table Расходные_материалы 
 (
 ID_Расходных_материалов tinyint identity(1,1) not null primary key,
 Название_материала varchar(50) not null,
 Колличество_оставшегося_материала int  
 )

 create table Услуга
 (
 Id_Услуги tinyint identity(1,1) not null primary key,
 Название_услуги varchar(50) not null
 )

 create table Процедура
 (
 Id_Процедуры tinyint identity(1,1) not null primary key,
Название_процедуры varchar(50) not null,
Кабинет int not null,
Дата_и_время date not null,
Id_Услуги tinyint not null constraint FK_PS foreign key (Id_Услуги) references Услуга( Id_Услуги),
ID_РАБОТНИК tinyint not null constraint FK_PC foreign key ( ID_РАБОТНИК) references Работник ( ID_РАБОТНИК),
ID_Пациента tinyint not null constraint FK_PA foreign key (ID_Пациента) references Пациент (ID_Пациента)
 )

 create table расход
 (
 Id_Расхода tinyint identity(1,1) not null primary key,
 Id_Услуги tinyint not null constraint FK_PZ foreign key (Id_Услуги) references Услуга( Id_Услуги),
 ID_Расходных_материалов tinyint not null constraint FK_PL foreign key (ID_Расходных_материалов) references Расходные_материалы (ID_Расходных_материалов),
 Количество_расходуемого_материала int  
 )
------------------------------------------
 go
 insert into Расходные_материалы(Название_материала,Колличество_оставшегося_материала) values
 ('Ватные палочки',1000),
 ('Перчатки',1000),
 ('Шприцы',1000),
 ('Одноразовые пробирки',1000),
 ('Ватные диски',1000),
 ('Гель для узи',1500),
 ('Дезинфектор',10000),
 ('Иглы',1500),
 ('Бахилы',2500),
 ('Одноразовые полотенца',10000),
 ('Пепетки',1500),
 ('Баночки для мочи',1000),
 ('Баночки для кала',1000),
 ('Реагент на ВИЧ',1000),
 ('Реагент на Гепатит В',1000),
 ('Реагент на ТГК',1000),
 ('Реагент на гепатит С',1000),
 ('Реагент на эритроциты',1000),
 ('Реагент на гомоглобин',1000),
 ('Реагент на тромбоцты',1000),
 ('Реагент на глюкозу',1000)
	
insert into Работник (Фамилия,Имя,Отчество,Номер_т,Должность) values
('Беганская','Анна','Сергеевна',7534521,'Сборщик крови'),
('Головко','Антон','Сергеевич',6584653,'Специалист по УЗИ'),
('Давидян','Артем','Арутинович',9654216,'Сборщик кала'),
('Джумайло','Владислав','Альбертович',4568432,'Сборщик мочи'),
('Дубинин','Олег','Александрович',4984324,'Сборщик гормонов щитовидной железы'),
('Исаев','Роман','Дмитриевич',6854325,'Сборщик гормонов репродуктивной системы'),
('Калиновский','Андрей','Михайлович',4653215,'Сборщик_крови'),
('Камаева','Яна','Владимировна',6531456,'Сборщица_крови')
insert into Пациент(Фамилия,Имя,Отчество,Номер_т,Дата_Рождения)values
('Карамутлу','Даниил','Махмутович',7953267,'05.03.1999'),
('Козлова','Полина','Влаславовна',7525133,'17.04.1998'),
('Коношенков','Константин','Васильевич',7472547,'26.03.1999'),
('Котиков','Максим','Вячеславович',7359164,'17.01.1998'),
('Нестерович','Илья','Сергеевич',7623841,'14.02.1998'),
('Саленко','Екатерина','Дмитриевна',8529614,'21.06.1999'),
('Сущеня','Владислав','Валентинович',6514851,'15.03.1999'),
('Секержитская','Анастасия','Александровна',2589641,'06.07.1999'),
('Телешь','Ян','Александрович',3458521,'30.12.1999'),
('Тихомиров','Борис','Константинович',1532585,'03.08.1999')
insert into Услуга(Название_услуги)values
('Клинический_анализ_крови'),
('Общий_анализ_крови'),
('Биохимический_анализ_крови'),
('Оприделение_группы_крови'),
('Анализ_крови_на_гепатит_В'),
('Анализ_крови_на_гепатит_С'),
('Анализ_крови_на_ВИЧ'),
('Анализ_крови_на_содержание_ТГК'),
('Анализ_гормонов_щитовидной_железы'),
('Анализ_гормонов_репродуктивной_системы'),
('Общий_анализ_мочи'),
('Общий_анализ_кала'),
('УЗИ_сердца'),
('УЗИ_поджелудочной_железы'),
('УЗИ_плода'),
('УЗИ_кишечника')


 insert into Расход( ID_Расходных_материалов,Id_Услуги,Количество_расходуемого_материала ) values
  (1,1,1),(2,1,1),(3,1,1),(4,1,1),(5,1,1), (7,1,1), (8,1,1), (9,1,1), (11,1,1), (18,1,1), (19,1,1), (20,1,1), (21,1,1),  
  (1,2,1), (2,2,1), (3,2,1), (4,2,1), (5,2,1), (7,2,1), (8,2,1), (9,2,1), (11,2,1), (18,2,1), (19,2,1), (20,2,1), (21,2,1),
  (1,3,1), (2,3,1), (3,3,1), (4,3,1), (5,3,1), (7,3,1), (8,3,1), (9,3,1), (11,3,1), (18,3,1), (19,3,1), (20,3,1), (21,3,1),
  (1,4,1), (2,4,1), (3,4,1), (4,4,1), (5,4,1), (7,4,1), (8,4,1), (9,4,1), (11,4,1),   
  (1,5,1), (2,5,1), (3,5,1), (4,5,1), (5,5,1), (7,5,1), (8,5,1), (9,5,1), (11,5,1), (15,5,1),
  (1,6,1), (2,6,1), (3,6,1), (4,6,1), (5,6,1), (7,6,1), (8,6,1), (9,6,1), (11,6,1), (17,6,1),
  (1,7,1), (2,7,1), (3,7,1), (4,7,1), (5,7,1), (7,7,1), (8,7,1), (9,7,1), (11,7,1), (14,7,1),
  (1,8,1), (2,8,1), (3,8,1), (4,8,1), (5,8,1), (7,8,1), (8,8,1), (9,8,1), (11,8,1), (16,8,1),
  (1,9,1), (2,9,1), (3,9,1), (4,9,1), (5,9,1), (7,9,1), (8,9,1), (9,9,1), (11,9,1),
  (1,10,1), (2,10,1), (3,10,1), (4,10,1), (5,10,1), (7,10,1), (8,10,1), (9,10,1), (11,10,1),
  (2,11,1), (4,11,1), (7,11,1), (9,11,1), (12,11,1),
  (2,12,1), (4,12,1), (7,12,1), (9,12,1), (13,12,1),
  (2,13,1), (5,13,1), (6,13,1), (7,13,1), (9,13,1), (11,13,1),
  (2,14,1), (5,14,1), (6,14,1), (7,14,1), (9,14,1), (11,14,1),
  (2,15,1), (5,15,1), (6,15,1), (7,15,1), (9,15,1), (11,15,1),
  (2,16,1), (5,16,1), (6,16,1), (7,16,1), (9,16,1), (11,16,1)
 
 insert into Процедура(Название_процедуры,Кабинет,Дата_и_время,ID_Пациента,ID_РАБОТНИК,Id_Услуги)values
('Клинический_анализ_крови',108,'18-12-2017 08:10',2,1,1)
----------------
go
create trigger Spis_tr
on Процедура after insert
as
 declare @id_usl int
 set @id_usl = (select Id_Услуги from inserted )
 declare @kol int, @raschod int
 set @kol = (select расход.Количество_расходуемого_материала from расход where расход.Id_Услуги = @id_usl)
 set @raschod = (select расход.ID_Расходных_материалов from расход where расход.Id_Услуги = @id_usl)
 update Расходные_материалы
 set Колличество_оставшегося_материала = Колличество_оставшегося_материала - @kol
 where ID_Расходных_материалов = @raschod
 print @id_usl
 print @kol
 print @raschod
 rollback tran
 
 go

 drop trigger Spis_tr



------------------
 go
create trigger Spis_t1
on Процедура after insert
as
 declare @id_usl int
 set @id_usl = (select Id_Услуги from Услуга where  Услуга.Id_Услуги = 1   )
 declare @kol int, @raschod int
 set @kol = (select расход.Количество_расходуемого_материала from расход where расход.Id_Услуги = @id_usl)
 set @raschod = (select расход.ID_Расходных_материалов from расход where расход.Id_Услуги = @id_usl)
 update Расходные_материалы
 set Колличество_оставшегося_материала = Колличество_оставшегося_материала - @kol
 where ID_Расходных_материалов = @raschod
 print @id_usl
 print @kol
 print @raschod
 
 select Id_Услуги from Услуга
 go

 drop trigger Spis_t1
24 дек 17, 21:56    [21058912]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
dante0101
Написанный мною триггер выдает: Вложенный запрос вернул больше одного значения.
потому что
set @kol = (select расход.Количество_расходуемого_материала from расход where расход.Id_Услуги = @id_usl)
24 дек 17, 23:24    [21059022]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
dante0101,

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

Поэтому, предлагаю убрать из таблицы Расходные_материалы колонку Колличество_оставшегося_материала. Вот так:
create table Расходные_материалы 
 (
 ID_Расходных_материалов tinyint identity(1,1) not null primary key,
 Название_материала varchar(50) not null
 )
GO
  insert into Расходные_материалы(Название_материала) values
 ('Ватные палочки'),
 ('Перчатки'),
 ('Шприцы'),
 ('Одноразовые пробирки'),
 ('Ватные диски'),
 ('Гель для узи'),
 ('Дезинфектор'),
 ('Иглы'),
 ('Бахилы'),
 ('Одноразовые полотенца'),
 ('Пепетки'),
 ('Баночки для мочи'),
 ('Баночки для кала'),
 ('Реагент на ВИЧ'),
 ('Реагент на Гепатит В'),
 ('Реагент на ТГК'),
 ('Реагент на гепатит С'),
 ('Реагент на эритроциты'),
 ('Реагент на гомоглобин'),
 ('Реагент на тромбоцты'),
 ('Реагент на глюкозу')


Добавить таблицу Приход_материалов вот так:
 CREATE TABLE Приход_материалов (
  Id_операции int IDENTITY(1,1) PRIMARY KEY,
  ID_Расходных_материалов tinyint not null constraint FK_PL2 foreign key (ID_Расходных_материалов) references Расходные_материалы (ID_Расходных_материалов),
  Количество INT,
  Комментарий nvarchar(30) -- Поступление, корректировка количества и т.п.
)
GO
 INSERT Приход_материалов (ID_Расходных_материалов, Количество, Комментарий) VALUES
 (1,1000,N'Начальное количество'),
 (2,1000,N'Начальное количество'),
 (3,1000,N'Начальное количество'),
 (4,1000,N'Начальное количество'),
 (5,1000,N'Начальное количество'),
 (6,1500,N'Начальное количество'),
 (7,10000,N'Начальное количество'),
 (8,1500,N'Начальное количество'),
 (9,2500,N'Начальное количество'),
 (10,10000,N'Начальное количество'),
 (11,1500,N'Начальное количество'),
 (12,1000,N'Начальное количество'),
 (13,1000,N'Начальное количество'),
 (14,1000,N'Начальное количество'),
 (15,1000,N'Начальное количество'),
 (16,1000,N'Начальное количество'),
 (17,1000,N'Начальное количество'),
 (18,1000,N'Начальное количество'),
 (19,1000,N'Начальное количество'),
 (20,1000,N'Начальное количество'),
 (21,1000,N'Начальное количество')


А для получения актуальных остатков сделать VIEW;
CREATE VIEW Остаток_расходных_материалов AS
SELECT M.ID_Расходных_материалов, M.Название_материала,
  ISNULL(I.Количество,0)-ISNULL(T.Количество,0) AS Остаток
FROM Расходные_материалы M
LEFT JOIN (SELECT ID_Расходных_материалов, SUM(Количество) AS Количество
  FROM Приход_материалов GROUP BY ID_Расходных_материалов) I ON I.ID_Расходных_материалов=M.ID_Расходных_материалов
LEFT JOIN (SELECT B.ID_Расходных_материалов, SUM(B.Количество_расходуемого_материала) AS Количество
  FROM Процедура P
  JOIN Расход B ON B.Id_Услуги=P.Id_Услуги
  GROUP BY B.ID_Расходных_материалов) T ON T.ID_Расходных_материалов=M.ID_Расходных_материалов
24 дек 17, 23:59    [21059048]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
invm
Member

Откуда: Москва
Сообщений: 9348
dante0101,

Никогда так не делайте:
ptr128
А для получения актуальных остатков сделать VIEW;
CREATE VIEW Остаток_расходных_материалов AS
SELECT M.ID_Расходных_материалов, M.Название_материала,
  ISNULL(I.Количество,0)-ISNULL(T.Количество,0) AS Остаток
FROM Расходные_материалы M
LEFT JOIN (SELECT ID_Расходных_материалов, SUM(Количество) AS Количество
  FROM Приход_материалов GROUP BY ID_Расходных_материалов) I ON I.ID_Расходных_материалов=M.ID_Расходных_материалов
LEFT JOIN (SELECT B.ID_Расходных_материалов, SUM(B.Количество_расходуемого_материала) AS Количество
  FROM Процедура P
  JOIN Расход B ON B.Id_Услуги=P.Id_Услуги
  GROUP BY B.ID_Расходных_материалов) T ON T.ID_Расходных_материалов=M.ID_Расходных_материалов
Помимо того, что запрос будет лопатить все приходы и расходы по затронутым материалам, действительно актуальные остатки можно получить только на TIL serializable.
25 дек 17, 12:26    [21059994]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
invm
dante0101,
Помимо того, что запрос будет лопатить все приходы и расходы по затронутым материалам, действительно актуальные остатки можно получить только на TIL serializable.

А Вам жалко? Я так понял, что тут за год и 100 тыс. операций не наберется. А раз в год все можно переносить в архив.
А так как актуальные остатки используются только для отчета, то тут я бы, скоре, READ UNCOMMITED использовал )
25 дек 17, 12:31    [21060004]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
invm
Member

Откуда: Москва
Сообщений: 9348
ptr128
А Вам жалко?
Мне нет. Если ТС предпочтет делать хрень, то его проблемы.
ptr128
А так как актуальные остатки используются только для отчета
Опять свой личный опыт экстраполируете на целый мир?
По-вашему того, кто хочет что-то забрать со склада, в принципе не должно интересовать а сколько этого что-то на складе есть?
25 дек 17, 13:12    [21060131]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
А в каких вузах учат писать
автор
ID_материалов


Это же безумие какое-то? Кто вообще автора учил объекты БД именовать кирилицей?
25 дек 17, 13:21    [21060159]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
Cammomile
Это же безумие какое-то? Кто вообще автора учил объекты БД именовать кирилицей?
и какой язык является "провославным" для именования объектов БД с вашей точки зрения?
25 дек 17, 13:40    [21060247]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
invm
По-вашему того, кто хочет что-то забрать со склада, в принципе не должно интересовать а сколько этого что-то на складе есть?

В постановке задачи ТС это как раз совершенно не интересно. Перечитайте внимательно, что он написал.
А как хотел ТС, так делать вообще не допустимо. Он собирался обновлять несколько записей одной таблицы в произвольном порядке в пределах одной транзакции, что гарантированно будет приводить к дидлокам.

Я понимаю Ваше желание продвигать более правильные подходы, с двух шаговой обработкой (резервирование по всем строкам, если успешно - то списание), но они далеко выходят за рамки задачи, поставленной ТС и уж точно не предназначены для реализации в триггере.
25 дек 17, 13:44    [21060269]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Дедушка
Cammomile
Это же безумие какое-то? Кто вообще автора учил объекты БД именовать кирилицей?
и какой язык является "провославным" для именования объектов БД с вашей точки зрения?

Cammomile, конечно, излишне категоричен. Но, с точки зрения повторного использования кода на других проектах, конечно, лучше ограничиваться латинскими буквами. А то сегодня заказчик из России, а завтра - уже из Бельгии или ОАЭ.
В случае ТС, это уж точно не имеет никакого значения, так что пусть называет объекты БД так, как ему заблагорассудится )
25 дек 17, 14:05    [21060375]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Cammomile
Это же безумие какое-то? Кто вообще автора учил объекты БД именовать кирилицей?

навеяло...
анекдот
Как вы так быстро изучили английский?!
Это было очень просто - он очень похож на SQL!
25 дек 17, 14:23    [21060470]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Начнем с того, что программист должен все делать рационально и разумно.
Именовать объекты бд кирилицей нерационально хотяб потому, что постоянно надо переключать раскладку, для написания непосредственно языковых инструкций.
Далее, даже если кодер 100% уверен, что его проект никто не будет использовать на международной арене, писать по-русски это просто дурной тон. Так уж исторически сложилось, что программирует весь мир по-английски. (Если не считать всякиз один-эсеров, у которых свое "пограмирование")
Далее, ежели все таки принято судьбоносное решение писать поля да таблицы кирилицей, то будь добр выдерживать стиль.
ID_объекта это такая же криворукая порнография как tsena_one_l_benzina или ItogoProdaz.
Про прочие разезды между аппер кемелом, снейккейсом и аппер снейком я вообще молчу.
Хочется взять и дать тапкой по рукам, за такие коды.


Триггеры у него не пишутся - научись код оформлять сначала, а потом в триггеры лезь!
25 дек 17, 14:26    [21060484]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
использовать кириллицу можно, только как потом отличать
Eng?RusEng?Rus
CС
НH
ОO
ТT
BВ
EЕ

etc и т.п.
25 дек 17, 14:37    [21060547]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Cammomile,

Не смотря на то, что с истинностью Ваших высказываний я согласен, хочу заметить, что Ваш тон и стиль скорее может отбить у новичка всякое желание изучать SQL, чем донести до него какие-то истины.
Какова цель этого Вашего поста?
25 дек 17, 14:38    [21060553]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Cammomile
Про прочие разезды между аппер кемелом, снейккейсом и аппер снейком я вообще молчу.

Camel-case знаю, Snake-case знаю, а вот про эти впервые слышу, что за звери такие?
25 дек 17, 14:39    [21060558]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Руслан Дамирович
Cammomile
Про прочие разезды между аппер кемелом, снейккейсом и аппер снейком я вообще молчу.

Camel-case знаю, Snake-case знаю, а вот про эти впервые слышу, что за звери такие?


UpperCamelCase
loverCamelCase
snake_case
UPPER_SNAKE_CASE

Какой_то_Хренокейс
25 дек 17, 14:51    [21060622]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Cammomile
CamelCase
snake_case

и dash-case (он же hypens)?
Итого, имеем как минимум 3 вида "валидных" "общепринятых" способов написания названий. Добавляем к ним некоторые вариации и уже получаем с десяток "общепринятых" способов.

Cammomile
Именовать объекты бд кирилицей нерационально хотяб потому, что постоянно надо переключать раскладку

Вот это нормальное обоснование. Все остальное - шелуха предрассудков.
25 дек 17, 14:59    [21060671]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Где шелуха? Я же не спорю за КемелКейс против снейк_кейса, я говорю, что стиль должен быть единый.

Какая-такая шелуха предрассудков? Ну давайте тогда,вообще, [Именовать объекты :: как попало], то-то будет вольница и расцвет молоды талантов!
25 дек 17, 15:03    [21060679]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Cammomile
[Именовать объекты :: как попало]

WHY нет? Нормальный такой стиль. Только нужно будет везде брекеты использовать, а 90% их не ставят потому что "код выглядит компактнее". А это переучиваться - не зайдет, короче.
25 дек 17, 15:07    [21060694]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
invm
Member

Откуда: Москва
Сообщений: 9348
ptr128
Он собирался обновлять несколько записей одной таблицы в произвольном порядке в пределах одной транзакции, что гарантированно будет приводить к дидлокам.
Теперь ТС испугается страшного слова и будет в цикле обновлять каждую строку в отдельной транзакции.
У вас классный хрустальный шар: не видя ни запроса, ни его плана - сразу ставите диагноз.
ptr128
и уж точно не предназначены для реализации в триггере.
Очередной триггерофоб...
25 дек 17, 16:08    [21060961]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
invm
Очередной триггерофоб...

Докажите, что я не прав, и UPDATE
ptr128
нескольких записей одной таблицы в произвольном порядке в пределах одной транзакции

из нескольких параллельных запросов не может привести к дидлоку.
25 дек 17, 16:15    [21060985]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
invm
Member

Откуда: Москва
Сообщений: 9348
ptr128
Докажите, что я не прав
Нет уж. Сначала вы доказывайте свою правоту насчет неприменимости триггеров.
ptr128
не может привести к дидлоку
Покажите место, где я утверждал, что "не может". Заодно поясните каким образом связаны триггер и дедлочащий update.
25 дек 17, 16:45    [21061074]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
invm
Заодно поясните каким образом связаны триггер и дедлочащий update.


IF EXISTS (SELECT 1 FROM sysobjects WHERE name=N'TEST_T' and type='U') DROP TABLE TEST_T 
IF EXISTS (SELECT 1 FROM sysobjects WHERE name=N'TEST_L' and type='U') DROP TABLE TEST_L
IF EXISTS (SELECT 1 FROM sysobjects WHERE name=N'TEST_Q' and type='U') DROP TABLE TEST_Q
IF EXISTS (SELECT 1 FROM sysobjects WHERE name=N'TEST_T_TR' and type='TR') DROP TRIGGER TEST_T_TR
GO
CREATE TABLE TEST_T (ID int)
CREATE TABLE TEST_L (ID int PRIMARY KEY NONCLUSTERED, QTY int)
--CREATE TABLE TEST_L (ID int PRIMARY KEY, QTY int)
CREATE TABLE TEST_Q (THREAD int, ID int)
GO
CREATE INDEX THREAD_IDX ON TEST_Q (THREAD)
GO
DECLARE @I int, @V int = 0
SET NOCOUNT ON
BEGIN TRAN
WHILE @V<1000001 BEGIN
  INSERT TEST_L (ID,QTY) VALUES (@V, 0)
  SELECT @V=@V+1
END
SELECT @V=0
WHILE @V<1001 BEGIN
  SELECT @I=0
  WHILE @I<11 BEGIN
    INSERT TEST_Q (THREAD, ID) VALUES (@I,CONVERT(int,RAND()*1000000))
    SELECT @I=@I+1
  END
  SELECT @V=@V+1
END
COMMIT TRAN
SET NOCOUNT OFF
GO
UPDATE STATISTICS TEST_Q WITH FULLSCAN
UPDATE STATISTICS TEST_L WITH FULLSCAN
GO
CREATE TRIGGER TEST_T_TR ON TEST_T AFTER INSERT AS
  UPDATE T
  SET QTY=T.QTY+CONVERT(int,RAND()*100)
  FROM TEST_L T
  JOIN TEST_Q Q ON Q.ID=T.ID AND Q.THREAD=CONVERT(int,RAND()*10)
GO


Двумя потоками запускаем
WHILE 1=1 INSERT TEST_T VALUES (1)


У меня через несколько секунд на одном из потоков выдало:
Msg 1205, Level 13, State 51, Procedure TEST_T_TR, Line 2
Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.


А теперь или извиняйтесь за наезд, или доказывайте Вы правы.
25 дек 17, 21:54    [21061621]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Рыбка не понимает, что не триггер приводит к deadlock, что тут поделать
26 дек 17, 09:35    [21062126]     Ответить | Цитировать Сообщить модератору
 Re: не удается написать триггер  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
TaPaK,

Чукча ни читатель, чукча писатель?

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

И совершенно не важно в триггере это будет происходить или нет.
26 дек 17, 09:46    [21062155]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить