Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 add at the same time delete

Author  Topic 

sher_amf
Starting Member

6 Posts
Posted - 2008-01-20 : 03:26:19
what i want is that everytime i add something to my sell table the stocks table deletes
here is the file i want that everytime i buy something the stocks would lessen..

http://rapidshare.com/files/85111869/add_at_the_same_time_delete.zip.html

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2008-01-20 : 03:38:55
you probably need a trigger to do the job.

Can you explain what you want here ? I am too lazy to download, extract and see that .

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

subrata4allfriends
Starting Member

24 Posts
Posted - 2008-01-20 : 05:20:54
Commonly we use trigger for ur requirement.

But, I think the trigger will slow down whole the process ... it's better to use stored procedure....

"While inserting data into sales table delete record from stock table" this is the basic tecnique.
Create a stored procedure....which will insert data into sales table.....then put a piece of code to delete the same form stock table into the stored procedure along with the insert statement.
Please use transaction while creating stored procedure..... for better result.

Please inform me in case of any issue regarding the same.


Thanks & Regards,
4allfriends.

"Life is not a bed of roses."
Go to Top of Page

sher_amf
Starting Member

6 Posts
Posted - 2008-01-20 : 05:29:09
ah how do you code that? can you show me where and how i apply that?
you can look at my codes
Go to Top of Page

sher_amf
Starting Member

6 Posts
Posted - 2008-01-20 : 05:30:36
quote:

you probably need a trigger to do the job.

Can you explain what you want here ? I am too lazy to download, extract and see that .

i have table stock
i have table for sales
so everytime i buy something from the stock table it will decrease
and the sales table will add
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-20 : 07:25:37
quote:
Originally posted by sher_amf

quote:

you probably need a trigger to do the job.

Can you explain what you want here ? I am too lazy to download, extract and see that .

i have table stock
i have table for sales
so everytime i buy something from the stock table it will decrease
and the sales table will add


Can you provide structure of your table?
Go to Top of Page

sher_amf
Starting Member

6 Posts
Posted - 2008-01-20 : 07:51:01
here as you can see there are 2 table..
the table above shows my stocks available...
the table below shows my table of the stocks i have bought
so everytime i buy stocks the table above will lessen and the table below will be added so its like moving the stocks from 1 table to the next
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-20 : 08:02:07
[code]DECLARE @SaleTemp table
( brokers varchar)50),
stock varchar(50),
qty int)


INSERT INTO Sales (brokers,stock,qty,..)
OUTPUT INSERTED.brokers,INSERTED.stock,INSERTED.qty INTO @SaleTemp
VALUES(xxx,yyy,....)

UPDATE b
SET b.qty=b.qty-t.qty
FROM brokers b
INNER JOIN @SaleTemp t
ON t.brokers=b.brokers
AND t.stock=b.stocks[/code]
Go to Top of Page

subrata4allfriends
Starting Member

24 Posts
Posted - 2008-01-20 : 08:33:33
quote:
Originally posted by sher_amf

ah how do you code that? can you show me where and how i apply that?
you can look at my codes


---------------------------------------------------------------------
--Start: SPUpdateSalesStock
---------------------------------------------------------------------

CREATE PROCEDURE SPUpdateSalesStock
(
@brokers VARCHAR(50),
@stock VARCHAR(50),
@qty INT,
@date DATETIME,
@price NUMERIC(10,2)
)
AS
BEGIN

DECLARE @Lclqty AS INT
DECLARE @LclError AS INT

BEGIN TRANSACTION

INSERT INTO Sales
(
brokers,
stock,
qty,
month,
day,
year,
price
)
VALUES
(
@brokers,
@stock,
@qty,
MONTH(@date),
DAY(@date),
YEAR(@date),
@price
)

SET @LclError = @@ERROR
IF @LclError <> 0
GOTO ErrorHandle

SELECT @Lclqty = qty
FROM Brokers
WHERE Brokers = @brokers
AND stocks = @stock


UPDATE Brokers
SET qty = @Lclqty - @qty
WHERE Brokers = @brokers
AND stocks = @stock

SET @LclError = @@ERROR
IF @LclError <> 0
GOTO ErrorHandle

COMMIT TRANSACTION
RETURN

ErrorHandle:
ROLLBACK TRANSACTION

END

---------------------------------------------------------------------
--End: SPUpdateSalesStock
---------------------------------------------------------------------


Thanks & Regards,
4allfriends.

"Life is not a bed of roses."
Go to Top of Page

subrata4allfriends
Starting Member

24 Posts
Posted - 2008-01-20 : 08:38:45
Please go through the code..........and as ur table/column name.

Feel free to inform me in case of any issue ragarding the same.

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-01-20 : 09:22:19
quote:
Originally posted by subrata4allfriends

quote:
Originally posted by sher_amf

ah how do you code that? can you show me where and how i apply that?
you can look at my codes


---------------------------------------------------------------------
--Start: SPUpdateSalesStock
---------------------------------------------------------------------

CREATE PROCEDURE SPUpdateSalesStock
(
@brokers VARCHAR(50),
@stock VARCHAR(50),
@qty INT,
@date DATETIME,
@price NUMERIC(10,2)
)
AS
BEGIN

DECLARE @Lclqty AS INT
DECLARE @LclError AS INT

BEGIN TRANSACTION

INSERT INTO Sales
(
brokers,
stock,
qty,
month,
day,
year,
price
)
VALUES
(
@brokers,
@stock,
@qty,
MONTH(@date),
DAY(@date),
YEAR(@date),
@price
)

SET @LclError = @@ERROR
IF @LclError <> 0
GOTO ErrorHandle

SELECT @Lclqty = qty
FROM Brokers
WHERE Brokers = @brokers
AND stocks = @stock

UPDATE Brokers
SET qty = @Lclqty - @qty
WHERE Brokers = @brokers
AND stocks = @stock

SET @LclError = @@ERROR
IF @LclError <> 0
GOTO ErrorHandle

COMMIT TRANSACTION
RETURN

ErrorHandle:
ROLLBACK TRANSACTION

END

---------------------------------------------------------------------
--End: SPUpdateSalesStock
---------------------------------------------------------------------


Thanks & Regards,
4allfriends.

"Life is not a bed of roses."


will this ensure you always get the qty that you have recently purchased out of particular stock & broker?
Go to Top of Page

sher_amf
Starting Member

6 Posts
Posted - 2008-01-20 : 18:36:45
okay my table's name is astock (available stocks)
table's name is stock(stocks i have bought)
here is the code for my buy this page shows a table of available stock and it is here where you make your transaction of buying stocks

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>

<script type="text/javascript" language="JavaScript">
<!-- Copyright 2002 Bontrager Connection, LLC

function getCalendarDate()
{
   var months = new Array(13);
   months[0]  = "January";
   months[1]  = "February";
   months[2]  = "March";
   months[3]  = "April";
   months[4]  = "May";
   months[5]  = "June";
   months[6]  = "July";
   months[7]  = "August";
   months  = "September";
   months[9]  = "October";
   months[10] = "November";
   months[11] = "December";
   var now         = new Date();
   var monthnumber = now.getMonth();
   var monthname   = months[monthnumber];
   var monthday    = now.getDate();
   var year        = now.getYear();
   if(year < 2000) { year = year + 1900; }
   var dateString = monthname +
                    ' ' +
                    monthday +
                    ', ' +
                    year;
   return dateString;
} // function getCalendarDate()

function getClockTime()
{
   var now    = new Date();
   var hour   = now.getHours();
   var minute = now.getMinutes();
   var second = now.getSeconds();
   var ap = "AM";
   if (hour   > 11) { ap = "PM";             }
   if (hour   > 12) { hour = hour - 12;      }
   if (hour   == 0) { hour = 12;             }
   if (hour   < 10) { hour   = "0" + hour;   }
   if (minute < 10) { minute = "0" + minute; }
   if (second < 10) { second = "0" + second; }
   var timeString = hour +
                    ':' +
                    minute +
                    ':' +
                    second +
                    " " +
                    ap;
   return timeString;
} // function getClockTime()

//-->
</script>


<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Buy</title>
<style type="text/css">
<!--
#Layer1 {
	position:absolute;
	width:155px;
	height:51px;
	z-index:1;
	left: 41px;
	top: 534px;
}
#Layer2 {
	position:absolute;
	width:155px;
	height:51px;
	z-index:2;
	left: 322px;
	top: 67px;
}
#Layer3 {
	position:absolute;
	width:195px;
	height:66px;
	z-index:3;
	left: 769px;
	top: 535px;
}
#Layer4 {
	position:absolute;
	width:159px;
	height:50px;
	z-index:4;
	left: 586px;
	top: 136px;
}
body {
	background-image: url(wallpaper.jpg);
}
#Layer5 {
	position:absolute;
	width:996px;
	height:60px;
	z-index:5;
	left: 7px;
	top: 30px;
}
.style1 {
	font-family: "Times New Roman", Times, serif;
	font-size: 36px;
	font-weight: bold;
}
#Layer6 {
	position:absolute;
	width:979px;
	height:109px;
	z-index:6;
	left: 19px;
	top: 168px;
}

#Layer8 {
	position:absolute;
	width:902px;
	height:116px;
	z-index:8;
	left: 16px;
	top: 287px;
}
#Layer9 {
	position:absolute;
	width:706px;
	height:66px;
	z-index:9;
	left: 30px;
	top: 142px;
}
.style2 {
	font-size: 18px;
	font-weight: bold;
}
#Layer10 {
	position:absolute;
	width:153px;
	height:56px;
	z-index:10;
	left: 782px;
	top: 152px;
}
#Layer11 {
	position:absolute;
	width:200px;
	height:115px;
	z-index:11;
	left: 777px;
	top: 27px;
}
#Layer7 {
	position:absolute;
	width:200px;
	height:115px;
	z-index:12;
	left: 260px;
	top: 482px;
}
-->
</style>
</head>

<body>
<%@ page import="java.sql.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.util.*"%>
<%
try { 
	Connection con; 
	Statement stmt;
    ResultSet rs;
    String URL,query;
	
	String year = request.getParameter("select5");
	
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    con = DriverManager.getConnection("jdbc:odbc:sherwin","","");
    stmt = con.createStatement();
   query = "SELECT brokers FROM sell";
  rs = stmt.executeQuery(query);
 
	con.close();
} catch (Exception e){
    System.out.println("SQL Exception : " + e.getMessage());
}

%>

<div class="style1" id="Layer5">
  <p>ONLINE PORTFOLIO TRACKING SYSTEM</p>
  <p><img src="border.jpg" width="982" height="24" /></p>
</div>
<div id="Layer8">
  <table width="898" height="109" border="1">
   <tr>
      <td width="279"><strong>Broker name </strong></td>
      <td width="359"><strong>Stock Code </strong></td>
      <td width="76"><strong>Price</strong></td>
      <td width="156"><strong>Available Stocks </strong></td>
    </tr> <%


String strgetcode = request.getParameter("viewcode");

%>


<%@ page import="java.sql.*"%>
<%
       try { String URL,query; Connection con; Statement stmt;
          ResultSet rs;

          URL = "jdbc:odbc:sherwin";
          query = "SELECT * FROM astocks";
		  

          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
          con = DriverManager.getConnection(URL,"","");
          stmt = con.createStatement();
          rs = stmt.executeQuery(query);
	  

	while(rs.next())
	{%>
		<tr> 
		<td> <%= rs.getString("brokers") %> </td>
		<td> <%= rs.getString("stocks") %> </td>
		<td> <%= rs.getString("price") %> </td>
		<td> <%= rs.getString("qty") %> </td>
		
		</tr>
<%	}con.close();

       } catch (Exception e)
       		{
          		out.println("SQL Exception : " + e.getMessage());
       		}%>


</table>

</div>
<div id="Layer7">
<form  method="post" action="checkbuy.jsp">
</div>
<div id="Layer9">
  <p class="style2">Broker name:
      <label>
      <input type="text" name="brokers" />
      </label> 
   Stock code: 
   <label>
   <input type="text" name="stock" />
   </label> 
  Quantity: 
  <input name="qty" type="text" size="10" />
  </p>
  <p class="style2">Date bought : Month 
    <select name="select3">
      <option value="Jan">January</option>
      <option value="Feb">February</option>
      <option value="Mar">March</option>
      <option value="April">April</option>
      <option value="May">May</option>
      <option value="June">June</option>
      <option value="July">July</option>
      <option value="Aug">August</option>
      <option value="Sept">September</option>
      <option value="Nov">November</option>
      <option value="Dec">December</option>
      </select>
  Day: 
  <select name="select4">
    <option value="1">1</option>
    <option value="2">2</option>
    <option value="3">3</option>
    <option value="4">4</option>
    <option value="5">5</option>
    <option value="6">6</option>
    <option value="7">7</option>
    <option value="8">8</option>
    <option value="9">9</option>
    <option value="10">10</option>
    <option value="11">11</option>
    <option value="12">12</option>
    <option value="13">13</option>
    <option value="14">14</option>
    <option value="15">15</option>
    <option value="16">16</option>
    <option value="17">17</option>
    <option value="18">18</option>
    <option value="19">19</option>
    <option value="20">20</option>
    <option value="21">21</option>
    <option value="22">22</option>
    <option value="23">23</option>
    <option value="24">24</option>
    <option value="25">25</option>
    <option value="26">26</option>
    <option value="27">27</option>
    <option value="28">28</option>
    <option value="29">29</option>
    <option value="30">30</option>
    <option value="31">31</option>
    </select>
  Year: 
  <select name="select5">
    <option value="2000">2000</option>
    <option value="2001">2001</option>
    <option value="2002">2002</option>
    <option value="2003">2003</option>
    <option value="2004">2004</option>
    <option value="2005">2005</option>
    <option value="2006">2006</option>
    <option value="2007">2007</option>
    <option value="2008">2008</option>
    <option value="2009">2009</option>
    </select>
  </p>
  <p class="style2">
    <label>
    <input type="submit" name="Submit" value="Buy" />
    </label>
  </p>
</div>
<div id="Layer10"><a href="main.jsp"><img src="home.gif" width="150" height="48" border="0" /></a></div>
</form>
<div id="Layer11">
<script type="text/javascript" language="JavaScript"><!--
var calendarDate = getCalendarDate();
var clockTime = getClockTime();
document.write('Date is ' + calendarDate);
document.write('<br>');
document.write('Time is ' + clockTime);
//--></script></div>
</body>
</html>
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site