0

the deal is to create trigger(function?) which would send updated value from one table and add to another

example:
table1
column:AccountID and 2 column: Money

table2:
column:AccountID and 2 column: Money

i create something like this:

CREATE TRIGGER money_from_vault
ON table1
FOR UPDATE
AS
DECLARE @Money Int,@AccountID varchar(10)
SELECT @Money = (SELECT Money FROM table1),@AccountID=(SELECT AccountID from table1)

IF(@Money!=0)
BEGIN
update table2 set Money=Money+@Money where AccountID=@AccountID
END

And it should work like that:
if table1.money is updated to non-zero value, function should add to table2.Money value from table1.money. (table2.money=table2.money+table1.money)
Afther this table1.money should = 0

its function created to work on mine game. it`s highly possible that simple updating table1.Money = 0 will not work. The reason is when some player leave vault in game opened the game doesnt allow to change money in it. so this code need to be protected from this becouse it can loop untin player close vault... it would be money=money+@money+money+@money....

i dont know what im doing wrong and i want to learn how to do such tasks for the brighter future of my game

could anyone help me?

Edited by Paser: n/a

2
Contributors
4
Replies
6
Views
7 Years
Discussion Span
Last Post by Paser
1

If you want to create a trigger in the update event do this.

create trigger money_from_vault
on table1
after update
as
DECLARE @Money Int
DECLARE @AccountID varchar(10)
SET @Money = (Select Money from Inserted)
SET @AccoundID = (Select AccountId from Inserted)
IF @Money != 0
BEGIN
UPDATE table2 SET Money=Money+@Money WHERE AccountID=@AccountID
END

I think that should do it.

0

Server: Msg 137, Level 15, State 1, Procedure money_from_vault, Line 9
Must declare the variable '@AccoundID'.

Error pop up with Your code..

sorry, my misteake. Accound != Account ;P Im testing if it works.

Edited by Paser: n/a

0

sadly, it doesnt work. i think scropt is fine but game wont allow a change.
it takes money from table1 and does nothing with table2...

0
--DECLARATIONS and function name!

CREATE Procedure zeny_do_banku 
	@AccountID		varchar(10),
	@Money			int,
	@add_time	int
AS
Begin
	SET NOCOUNT ON
	SET	XACT_ABORT ON
	DECLARE		@Result		tinyint
	DECLARE @Money= (Select Money From ExtendedWarehouse where AccountID=@AccountID)
	DECLARE @add_time(SELECT add_time FROM ExtendedWarehouse where AccountID=@AccountID)
	DECLARE @Zmienna int
	SET @Zmienna=0
--====================================================================================
--	 chceck if bank account exists - if not -> add
--====================================================================================
If NOT EXISTS (SELECT AccountID FROM vault WHERE AccountID=@AccountID)
	begin
	INSERT INTO dbo.vault(AccountID,Money,add_time) 
	VALUES(@AccountID, 0, NULL)
	end 

--====================================================================================
--	 main function	
--====================================================================================
If EXISTS ( SELECT  AccountID  FROM  ExtendedWarehouse WHERE AccountID = @AccountID AND Money!=0) --it there is some money in vault
	begin 
	@zmienna=@Money  --save money value to variable @zmienna
	goto zerowanie --and go to the next step
	end 

zerowanie: --make money in vault=0
	if(@zmienna!=0) --if there really was something in vault
		begin
		set @Money = 0 --zero money in vault
		goto zmiana
		end

zmiana: 
	if(@Money!=0) --if money!=0 -> try to make it zero again
		begin
		goto zerowanie
		end
	else  -- if money=0 -> go to the next step
		begin
		goto wrzucanie_do_banku
		end

wrzucanie_do_depo: --the next step is to put money to other table
	if(@zmienna!=0)-- third time checking if there was gold in vault
		begin
		update dbo.vault set Money=Money+@zmienna where AccountID=@AccountID
		end
End
 GO

this code should work like that:
if there is some money in extendedwarehouse table -> save it to some variable
try to make Money=0 in extendedwarehouse until it`s really =0. if not -> loop back and try to make it zero again
if its really zero, put saved money from variable to other table(vault) to Money column
primary key is AccountID

Do You guys know if it will work? some changes? dont want to test it before any opinion becouse its mine first script like that
and i dont want any errors in my database becouse players wont be able to play my game
to make it clear (if someone has opened vault in game, money value cant be changed in database! function has to wait until player close it..)

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.