SQL Server Performance

add at the same time delete

Discussion in 'SQL Server 2005 Database Mirroring' started by sher_amf, Jan 18, 2008.

  1. sher_amf New Member

    what i want is that everytime i add something to my sell table the stocks table deletes
    Code:
    <html>
<body>
<%@ page import="java.sql.*"%>
<%@ page import="java.util.*"%>
<%@ page import="java.util.*"%>
<%
try { 
 Connection con; 
 Statement stmt;
    ResultSet rs;
    String brokers = request.getParameter("brokers");
    String stock = request.getParameter("stock");
 String qty = request.getParameter("qty");
 String price = request.getParameter("price");
 
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    con = DriverManager.getConnection("jdbc:odbc:sherwin","","");
    stmt = con.createStatement();
    
 rs = stmt.executeQuery("INSERT INTO sell (brokers,stock,qty,price) VALUES ('"+brokers+"','"+stock+"','"+qty+"','"+price+"')");
 rs = stmt.executeQuery("DELETE FROM stocks WHERE brokers = 'one'");
 
 
 response.sendRedirect("main.jsp");
 con.close();
} catch (Exception e){
    System.out.println("SQL Exception : " + e.getMessage());
}
response.sendRedirect("main.jsp");
%>
</body>
</html>
    sher_amf, Jan 18, 2008
    #2

  2. Adriaan New Member

    Just concatenate the INSERT and DELETE statements into a single script that you execute, perhaps separated with a semicolon ";" - but do check out if your ODBC driver supports this, and that your Java class doesn't freak out. For SQL Server by itself, there's no problem combining two statements in one script.
    If this is the only way that you're inserting into the sell table, then you could also add an INSERT trigger on the sell table - see the CREATE TRIGGER syntax in Books Online.
    Adriaan, Jan 19, 2008
    #3

  3. Madhivanan Moderator

    Also instead of concatenated sql, make use of procedure with parameters
    Madhivanan, Jan 23, 2008
    #4

  4. FrankKalis Moderator

    Yes, create INSERT and DELETE stored procedures and just feed them with parameters.
    FrankKalis, Jan 23, 2008
    #5

Share This Page