Re: How to desgin version control & primiary key ?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 10/31/04 

Date: Sun, 31 Oct 2004 10:15:06 -0500

"Agnes" <agnes@dynamictech.com.hk> wrote in message
news:umALmXvvEHA.1524@TK2MSFTNGP09.phx.gbl...
>
> I got an InvoiceTable with the primary key Invno, InvoiceChgtable will
keep
> the charges , noofunit, unitpirce.. etc, its primary key is using
'smallint'
> with increment YES

    Are you certain this will give you enough space to store all of the
changes over a long period? Why not use an INT instead? The SMALLINT will
only give you 32767 possible rows in the table; you'll use that up more
quickly than you think.

> 1) Should I use store procedure to insert the previous verson data or
> trigger rule ?
> If using trigger rule ? How can I do that ? (I never use trigger
> before )

    I would recommend using a trigger to handle this situation. The reason
is that you may require different stored procedures for various business
logic, and if you need to record history from each stored procedure that
will mean that you'll have to duplicate the logic across all of your
procedures. However, with a trigger you'll be able to centralize the logic
in one place. You should look up CREATE TRIGGER in Books Online and review
the syntax; they're not too difficult to use... What you'll do is create a
trigger FOR UPDATE that inserts all of the rows from the virtual 'deleted'
table (which appears within every trigger) into your history table.

> 2) If I want to keep the prevoius verson , How should I design the primary
> in invoiceTable ?? Invoiceno with suffix? such as (INV0001-XX) XX is the
> version ??

    Why would you want to store the version number in the invoice table?
Does the version number matter for your business logic?

> 3) for some other tables, I got 3-relationship , parent , child and
> grandchild, I got no idea how to design the primiary key in grandchild .

    Please post DDL for those tables. (
http://www.aspfaq.com/etiquette.asp?id=5006 ) 

-- 
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Relevant Pages

  • Re: Trigger, alternative way to pass variable to trigger
    ... What we did was have our object that we used to call stored procedures add ... Then in the trigger or default constraints, I used a user defined function ... system function, though just calling it as master.. ... >>Pro SQL Server 2000 Database Design ...
    (microsoft.public.sqlserver.programming)
  • Re: SPs or triggers
    ... would be sending ad-hoc query statements from your application to SQL Server ... Stored procedures help you abstract your ... To import user data by registration whit SP or Trigger? ... > @ValuePrice BIGINT, ...
    (microsoft.public.sqlserver.programming)
  • "on exception" statement not firing within stored procedure when called from trigger
    ... I am experiencing a problem whereby ON EXCEPTION statements are ... not firing within stored procedures when the stored procedures ... are invoked from a trigger. ... create table tBar ) lock mode row; ...
    (comp.databases.informix)
  • tcl sqlite stored procedures and triggers
    ... i was just looking at stored procedures and triggers. ... is some of this validation done at the form table level on an sqlite db? ... i scammed my ideas for triggers and stored procs from sqlitemanager on ... here was the code i used to make a trigger and stored procedure. ...
    (comp.lang.tcl)
  • Re: Permission tracking
    ... There is no primary key, lots of colums does allow NULL's ... create a trigger on this table and start to manipuilate with DELETED ... >> I suggest you using STORED PROCEDURES for security reasons. ...
    (microsoft.public.sqlserver.security)