MainCreateTable

use IlnurKurs

go

DROP TABLE Uchet
DROP TABLE Inventory
DROP TABLE Sklad
go
CREATE TABLE Sklad
(
    number_of_sklad int identity,
    otvetstvenny nvarchar(30) NOT NULL,
    constraint PK_numb_of_sklad primary key(number_of_sklad)
);
go

CREATE TABLE Inventory
(
    number_of_sklad int identity,
    kod_mat int,
    ed_izm nvarchar(10) constraint DF_izm default 'sht',
    kolvo_na_sklade int,
    data_last_op date,
    constraint PK_numbnumb_sklmat primary key(number_of_sklad, kod_mat)
);
go
CREATE TABLE Uchet
(
    number_of_sklad int,
    number_doc int,
    number_dog int,
    kod_mat int,
    ed_izm nvarchar(10) constraint DF_izm1 default 'sht',
    kolvo_mat int constraint CK_kolvo check(kolvo_mat>=0),
    date_of_post date,
    constraint PK_numbnumb_skldoc primary key(number_of_sklad, number_doc),
    constraint FK_numb_sklad_uch foreign key (number_of_sklad) references Sklad (number_of_sklad) on delete cascade on update cascade,
    constraint FK_numb_inv_skl_uch foreign key (number_of_sklad,kod_mat) references Inventory (number_of_sklad,kod_mat)
);
go
delete from Uchet;
delete from Inventory;
delete from Sklad;
go

set identity_insert Sklad on;
insert into Sklad(number_of_sklad,otvetstvenny)
values
(1,'Ivanov'),
(2,'Petrov'),
(3,'Sidorov'),
(4,'Rakushkin'),
(5,'Zavhozoff'),
(6,'Malishev');

set identity_insert Sklad off;
go
set identity_insert Inventory on;
insert into Inventory(number_of_sklad,kod_mat,ed_izm,kolvo_na_sklade,data_last_op)
values
(1,1,'ed',432,'2021-04-12'),
(2,1,'ed',500,'2019-05-23'),
(3,5,'ed',789,'2020-08-07'),
(3,8,'ed',122,'2018-09-01'),
(5,2,'ed',1000,'2021-09-05'),
(5,7,'ed',325,'2020-03-19');
set identity_insert Inventory off;
go
--set identity_insert Uchet on;
insert into Uchet(number_of_sklad,number_doc,number_dog,kod_mat,ed_izm,kolvo_mat,date_of_post)
values
(1,172434,190,1,'ed',22,'2021-04-12'),
(2,172234,190,1,'ed',22,'2020-07-14'),
(3,3934,190,5,'ed',22,'2012-03-12'),
(3,93434,190,8,'ed',22,'2018-01-12'),
(5,02434,190,2,'ed',22,'2019-09-21'),
(5,12434,190,7,'ed',22,'2020-10-17');
--set identity_insert Uchet off;
go
CREATE TRIGGER Deleting on Inventory
instead of delete
as
begin
    delete from Uchet
    where number_of_sklad in (select number_of_sklad from deleted) and kod_mat in (select kod_mat from deleted)
    delete from Uchet
    where number_of_sklad in (select number_of_sklad from deleted) and kod_mat in (select kod_mat from deleted)
end

go
Create TRIGGER UPD_Inventory ON Inventory
AFTER UPDATE
AS
BEGIN

    update Uchet
    set kolvo_mat = kolvo_mat-((select kolvo_na_sklade from deleted
                                            where number_of_sklad in (select number_of_sklad from inserted)
                                                and kod_mat in (select kod_mat from inserted))
    - (select kolvo_na_sklade from inserted
                                            where number_of_sklad in (select number_of_sklad from inserted)
                                                and kod_mat in (select kod_mat from inserted)))
    where number_of_sklad in (select number_of_sklad from inserted) and kod_mat in (select kod_mat from inserted)


END