Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table Склады(
- id_sklad int not null identity(1,1),
- fam varchar(30),
- constraint pk_Склады primary key(id_sklad),
- )
- create table Материалы(
- id_material int not null,
- name varchar(30),
- ed_izm varchar(15) default N'шт',
- price int check(price>0),
- constraint pk_Материалы primary key(id_material)
- )
- create table Наличие_материалов_на_складе(
- id_sklad int not null,
- id_material int not null,
- ed_izm varchar(15) default N'шт',
- kolvo int check (kolvo>=0),
- date_operation date,
- constraint fk_nal_sklad foreign key(id_sklad)
- references Склады(id_sklad),
- constraint fk_nal_material foreign key(id_material)
- references Материалы(id_material)
- on delete cascade on update cascade,
- constraint pk_Налмат primary key(id_sklad,id_material)
- )
- Триггеры:
- --Удаление
- CREATE TRIGGER SkladsTrig ON Склады
- INSTEAD OF DELETE AS
- BEGIN
- DELETE FROM Наличие_материалов_на_складе
- WHERE id_sklad in (SELECT id_sklad FROM deleted)
- DELETE FROM Склады
- WHERE id_sklad in (SELECT id_sklad FROM deleted)
- END
- --Обновление
- CREATE TRIGGER MaterialTrig ON Материалы
- after update AS
- IF(update(ed_izm))
- BEGIN
- update Наличие_материалов_на_складе
- set ed_izm =
- (select ed_izm from inserted
- where inserted.id_material = Наличие_материалов_на_складе.id_material)
- where id_material in (select id_material
- from inserted)
- END
- Проверка триггеров:
- update Материалы
- SET ed_izm = 'л'
- where ed_izm = 'кг'
- delete from Склады
- where id_sklad = 1
- Заполнение
- insert into Склады(fam)
- values('Petrov'),
- ('Arshavin'),
- ('Zinchenko'),
- ('Ovechkin'),
- ('Kozlov'),
- ('Ivanov')
- insert into Материалы(id_material,name,ed_izm,price)
- values(1,'Кирпич', 'шт', 13),
- (2,'Цемент','кг', 6),
- (3,'Краска','л', 160),
- (4,'Анкер-клин','шт', 5),
- (5,'Профнастил', 'м^2', 224),
- (6,'Утеплитель', 'мм', 600)
- insert into Наличие_материалов_на_складе(id_sklad, id_material, ed_izm, kolvo, date_operation)
- values(1,1,'шт', 10000, '22/11/18'),
- (2,2,'кг', 1000,'21/10/18'),
- (3,3,'л', 1500,'20/09/18'),
- (4,4,'шт', 500,'19/08/18'),
- (5,5,'м^2', 100,'18/07/18'),
- (6,6,'мм', 200,'17/06/18')
- Процедура:
- CREATE PROCEDURE EndPrice(
- @name varchar(15),
- @End_Price int = 0 output )--если хотим вернуть
- AS
- BEGIN
- select @End_Price = M.price * NM.kolvo
- from Материалы M, Наличие_материалов_на_складе NM
- where M.id_material = NM.id_material
- and @name = M.name
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement