Advertisement
lukifrancuz

Untitled

Jan 12th, 2023
1,781
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.01 KB | None | 0 0
  1. use TSQL2012
  2.  
  3. create table Historia(
  4.     id int identity(1,1) not null,
  5.     tabela varchar(20) not null,
  6.     operacja varchar(20) not null,
  7.     recordid int not null,
  8.     oldvalue varchar(100) not null,
  9.     newvalue varchar(100) not null,
  10.     dataoperacji date not null
  11.     )
  12. go
  13.  
  14. --1--
  15. --tu powinny być transakcje i wyjątki ale nie umiem
  16.  
  17. select * from Production.Products
  18. select * from Sales.OrderDetails where productid = 1
  19. go
  20.  
  21. create trigger t_prod_delete
  22. on Production.Products
  23. instead of delete
  24. as
  25. begin
  26.     declare @productid int;
  27.     set @productid = (select productid from deleted);
  28.  
  29.     delete from Sales.Orders where orderid in (select orderid from Sales.OrderDetails where productid = @productid);
  30.     delete from Sales.OrderDetails where productid = @productid;
  31.     delete from Production.Products where productid = @productid;
  32.  
  33.     insert into Historia(tabela, operacja, recordid, oldvalue, newvalue, dataoperacji)
  34.         values ('Products', 'delete', @productid, null, null, getdate());
  35. end
  36. go
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement