SHARE
TWEET

Untitled

Radio_jazz Oct 23rd, 2019 148 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create table Склады(
  2. id_sklad int not null identity(1,1),
  3. fam varchar(30),
  4. constraint pk_Склады primary key(id_sklad),
  5. )
  6.  
  7. create table Материалы(
  8. id_material int not null,
  9. name varchar(30),
  10. ed_izm varchar(15) default N'шт',
  11. price int check(price>0),
  12. constraint pk_Материалы primary key(id_material)
  13. )
  14.  
  15. create table Наличие_материалов_на_складе(
  16. id_sklad int not null,
  17. id_material int not null,
  18. ed_izm varchar(15) default N'шт',
  19. kolvo int check (kolvo>=0),
  20. date_operation date,
  21. constraint fk_nal_sklad foreign key(id_sklad)
  22.     references Склады(id_sklad),
  23. constraint fk_nal_material foreign key(id_material)
  24.     references Материалы(id_material)
  25.     on delete cascade on update cascade,
  26.  
  27. constraint pk_Налмат primary key(id_sklad,id_material)
  28. )
  29.  
  30.  
  31.  
  32. Триггеры:
  33.  
  34. --Удаление
  35. CREATE TRIGGER SkladsTrig ON Склады
  36. INSTEAD OF DELETE AS
  37. BEGIN
  38.     DELETE FROM Наличие_материалов_на_складе
  39.     WHERE id_sklad in (SELECT id_sklad FROM deleted)
  40.    
  41.     DELETE FROM Склады
  42.     WHERE id_sklad in (SELECT id_sklad FROM deleted)
  43. END
  44.  
  45. --Обновление
  46. CREATE TRIGGER MaterialTrig ON Материалы
  47. after update AS
  48. IF(update(ed_izm))
  49. BEGIN
  50.  update Наличие_материалов_на_складе
  51.    set ed_izm =
  52.       (select ed_izm from inserted
  53.       where inserted.id_material = Наличие_материалов_на_складе.id_material)
  54.    where id_material in (select id_material
  55.                     from inserted)
  56. END
  57.  
  58.  
  59.  
  60. Проверка триггеров:
  61.  
  62.  
  63. update Материалы
  64. SET ed_izm = 'л'
  65. where ed_izm = 'кг'
  66.  
  67. delete from Склады
  68. where id_sklad = 1
  69.  
  70.  
  71. Заполнение
  72.  
  73. insert into Склады(fam)
  74. values('Petrov'),
  75. ('Arshavin'),
  76. ('Zinchenko'),
  77. ('Ovechkin'),
  78. ('Kozlov'),
  79. ('Ivanov')
  80.  
  81. insert into Материалы(id_material,name,ed_izm,price)
  82. values(1,'Кирпич', 'шт', 13),
  83. (2,'Цемент','кг', 6),
  84. (3,'Краска','л', 160),
  85. (4,'Анкер-клин','шт', 5),
  86. (5,'Профнастил', 'м^2', 224),
  87. (6,'Утеплитель', 'мм', 600)
  88.  
  89.  
  90. insert into Наличие_материалов_на_складе(id_sklad, id_material, ed_izm, kolvo, date_operation)
  91. values(1,1,'шт', 10000, '22/11/18'),
  92. (2,2,'кг', 1000,'21/10/18'),
  93. (3,3,'л', 1500,'20/09/18'),
  94. (4,4,'шт', 500,'19/08/18'),
  95. (5,5,'м^2', 100,'18/07/18'),
  96. (6,6,'мм', 200,'17/06/18')
  97.  
  98.  
  99. Процедура:
  100.  
  101. CREATE PROCEDURE EndPrice(
  102.    @name varchar(15),
  103.    @End_Price int = 0  output )--если хотим вернуть
  104. AS
  105. BEGIN
  106.    select @End_Price = M.price * NM.kolvo
  107.    from Материалы M, Наличие_материалов_на_складе NM
  108.    where M.id_material = NM.id_material
  109.    and @name = M.name
  110. END
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top