142 lines
5.7 KiB
Transact-SQL
142 lines
5.7 KiB
Transact-SQL
|
|
-- This script creates a stored procedure that inserts data into the Players, Cards and Decks tables.
|
|
-- The procedure is wrapped in a transaction and uses a try-catch block to ensure that all inserts are successful or none are.
|
|
-- If an error occurs, the transaction is rolled back and no data is inserted.
|
|
|
|
create or alter procedure all_or_nothing_insert
|
|
as
|
|
begin
|
|
set nocount on
|
|
declare @player1 int, @player2 int, @card1 int, @card2 int
|
|
begin transaction
|
|
begin try
|
|
insert into Players(name, totalMatches, totalWins, totalLosses, totalDraws, reward_points, scraps, ores, meteorite_powder, rank) values ('PlayerTest1',10,5,3,2,100,100,100,100,1);
|
|
insert into Players(name, totalMatches, totalWins, totalLosses, totalDraws, reward_points, scraps, ores, meteorite_powder, rank) values ('PlayerTest2',20,10,6,4,200,200,200,200,2);
|
|
|
|
insert into Cards(name, faction, description, action, image, cost, type, power, color, cardset) values ('CardTest1',1,'Test card 1','Test action 1','Test image 1',1,'Unit',1,'Bronze',1);
|
|
insert into Cards(name, faction, description, action, image, cost, type, power, color, cardset) values ('CardTest2',2,'Test card 2','Test action 2','Test image 2',2,'special',2,'gold',2);
|
|
|
|
set @player1 = (select hmy from Players where name = 'PlayerTest1');
|
|
set @player2 = (select hmy from Players where name = 'PlayerTest2');
|
|
set @card1 = (select hmy from Cards where name = 'CardTest1');
|
|
set @card2 = (select hmy from Cards where name = 'CardTest2');
|
|
|
|
insert into CardsOwnership(hPlayer, hCard, nrOfCards, nrOfPremiumCards) values (@player1, @card1, 1, 1);
|
|
insert into CardsOwnership(hPlayer, hCard, nrOfCards, nrOfPremiumCards) values (@player2, @card2, 1, 1);
|
|
|
|
|
|
commit transaction
|
|
print 'All inserts succeeded'
|
|
end try
|
|
begin catch
|
|
rollback transaction
|
|
end catch
|
|
end
|
|
go
|
|
exec all_or_nothing_insert
|
|
go
|
|
|
|
-- This script creates a stored procedure that inserts data into the Players, Cards and Decks tables.
|
|
-- The procedure is wrapped in a transaction and uses savepoints to ensure that if an error occurs, only the failed insert is rolled back.
|
|
-- The procedure also uses a try-catch block to handle errors and print messages to the console.
|
|
|
|
create or alter procedure partial_insert
|
|
as
|
|
begin
|
|
set nocount on
|
|
declare @player1 int, @player2 int, @card1 int, @card2 int, @savepoint1 int, @savepoint2 int
|
|
set @savepoint1 = 1
|
|
set @savepoint2 = 1
|
|
|
|
begin transaction
|
|
begin try
|
|
insert into Players(name, totalMatches, totalWins, totalLosses, totalDraws, reward_points, scraps, ores, meteorite_powder, rank) values ('PlayerTest1',10,5,3,2,100,100,100,100,1);
|
|
insert into Players(name, totalMatches, totalWins, totalLosses, totalDraws, reward_points, scraps, ores, meteorite_powder, rank) values ('PlayerTest2',20,10,6,4,200,200,200,200,2);
|
|
save transaction InsertPlayers
|
|
set @savepoint1 = 0
|
|
|
|
insert into Cards(name, faction, description, action, image, cost, type, power, color, cardset) values ('CardTest1',1,'Test card 1','Test action 1','Test image 1',1,'Unit',1,'Bronze',1);
|
|
insert into Cards(name, faction, description, action, image, cost, type, power, color, cardset) values ('CardTest2',2,'Test card 2','Test action 2','Test image 2',2,'special',2,'gold',2);
|
|
save transaction InsertCards
|
|
set @savepoint2 = 0
|
|
|
|
set @player1 = (select hmy from Players where name = 'PlayerTest1');
|
|
set @player2 = (select hmy from Players where name = 'PlayerTest2');
|
|
set @card1 = (select hmy from Cards where name = 'CardTest1');
|
|
set @card2 = (select hmy from Cards where name = 'CardTest2');
|
|
|
|
insert into CardsOwnership(hPlayer, hCard, nrOfCards, nrOfPremiumCards) values (@player1, @card1, 1, 1);
|
|
insert into CardsOwnership(hPlayer, hCard, nrOfCards, nrOfPremiumCards) values (@player2, @card2, 1, 1);
|
|
|
|
commit transaction
|
|
print 'All inserts succeeded'
|
|
end try
|
|
begin catch
|
|
if @@TRANCOUNT > 0
|
|
begin
|
|
if @savepoint1 = 1
|
|
begin
|
|
print 'Players insert failed'
|
|
rollback tran
|
|
print error_message()
|
|
end
|
|
else if @savepoint2 = 1
|
|
begin
|
|
print 'Cards insert failed'
|
|
rollback tran InsertPlayers
|
|
print error_message()
|
|
commit tran
|
|
end
|
|
else
|
|
begin
|
|
print 'CardsOwnership insert failed'
|
|
print error_message()
|
|
rollback tran InsertCards
|
|
commit tran
|
|
end
|
|
end
|
|
else
|
|
print error_message()
|
|
end catch
|
|
end
|
|
go
|
|
exec partial_insert
|
|
|
|
-- This script deletes all the inserted data from the Players, Cards and Decks tables.
|
|
|
|
go
|
|
create or alter proc delete_added
|
|
as
|
|
begin
|
|
begin try
|
|
declare @player1 int, @player2 int, @card1 int, @card2 int, @cardOwnership1 int, @cardOwnership2 int
|
|
set @player1 = (select hmy from Players where name = 'PlayerTest1');
|
|
set @player2 = (select hmy from Players where name = 'PlayerTest2');
|
|
set @card1 = (select hmy from Cards where name = 'CardTest1');
|
|
set @card2 = (select hmy from Cards where name = 'CardTest2');
|
|
if @player1 is not null and @player2 is not null and @card1 is not null and @card2 is not null
|
|
begin
|
|
delete from CardsOwnership where hCard = @card1 and hPlayer = @player1
|
|
delete from CardsOwnership where hCard = @card2 and hPlayer = @player2
|
|
end
|
|
if @card1 is not null and @card2 is not null
|
|
begin
|
|
delete from Cards where hmy = @card1
|
|
delete from Cards where hmy = @card2
|
|
end
|
|
if @player1 is not null and @player2 is not null
|
|
begin
|
|
delete from Players where hmy = @player1
|
|
delete from Players where hmy = @player2
|
|
end
|
|
end try
|
|
begin catch
|
|
print error_message()
|
|
delete from Cards where name = 'CardTest1'
|
|
delete from Cards where name = 'CardTest2'
|
|
delete from Players where name = 'PlayerTest1'
|
|
delete from Players where name = 'PlayerTest2'
|
|
end catch
|
|
end
|
|
go
|
|
exec delete_added |