Professional Web Applications Themes

newbie - trigger - syntax - Microsoft SQL / MS SQL Server

Hi, Further to my previous question on syntax, I have a question regarding the syntax of a trigger. This question is probably more to do with general TSQL syntax than triggers but please bare with me. I realise that this probably isn't the best design in the world but I figure its a good learning exercise and I can always change it later. I have two tables: PucrhaseOrder - Among others it has a bit field named "Complete". LineItems - This contains (among others) = "PO_ID" "DeliveryDate" "DeliveryReference" "InvoiceDate" "InvoiceRef" There is a many to one relationship between the tables ...

  1. #1

    Default newbie - trigger - syntax

    Hi,

    Further to my previous question on syntax, I have a question regarding the
    syntax of a trigger. This question is probably more to do with general TSQL
    syntax than triggers but please bare with me.

    I realise that this probably isn't the best design in the world but I figure
    its a good learning exercise and I can always change it later.

    I have two tables:

    PucrhaseOrder - Among others it has a bit field named "Complete".
    LineItems - This contains (among others) =
    "PO_ID"
    "DeliveryDate"
    "DeliveryReference"
    "InvoiceDate"
    "InvoiceRef"

    There is a many to one relationship between the tables in that a PO may have
    many LineItems.

    What I would like the trigger to do is after a lineitem row is updated, the
    trigger checks to see if DeliveryDate/Ref, InvoiceDate/Ref are not null for
    every record in the LineItems table with a PO of X. If they all not null
    then it updates the "Complete" field in the PO table to true.

    However, I'm not really sure how to do this. The code I have so far is:

    Create Trigger trigMarkPOComplete
    On dbo.tblLineItems
    For Update
    As

    Its the code to check the value of the fields I'm not sure about. If anyone
    could point me in the right direction I'd be very grateful. I'm afraid my
    TSQL syntax is appalling. :)

    Any advice is gratefully received.

    Cheers

    Chris Strug


    Chris Guest

  2. #2

    Default Re: newbie - trigger - syntax

    It helps if you post DDL (CREATE TABLE statements with your questions).

    Here's my assumption of what your tables look like:

    CREATE TABLE PurchaseOrders (po_id INTEGER PRIMARY KEY, complete BIT NOT
    NULL)

    CREATE TABLE LineItems (po_id INTEGER NOT NULL REFERENCES PurchaseOrders
    (po_id), item_id INTEGER NOT NULL, deliverydate DATETIME NULL,
    deliveryreference INTEGER NULL, invoicedate INTEGER NULL, invoiceref INTEGER
    NULL, PRIMARY KEY (po_id, item_id))

    First, this table appears not to be in Third Normal Form. You have both
    invoicedate, invoiceref, deliverydate and deliveryref in this table when
    logically you would expect just the two ref columns and then put the dates
    in an Invoice table and a Deliveries table. I presume you wouldn't expect
    different dates for the same invoice/delivery ref?

    Ignoring the design problem for the moment, the trigger you want would look
    something like this:




    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Chris Strug" <com> wrote in message
    news:phx.gbl... 
    TSQL 
    figure 
    have 
    the 
    for 
    anyone 


    David Guest

  3. #3

    Default Re: newbie - trigger - syntax

    It helps if you post DDL (CREATE TABLE statements with your questions).

    Here's my assumption of what your tables look like:

    CREATE TABLE PurchaseOrders (po_id INTEGER PRIMARY KEY, complete BIT NOT
    NULL)

    CREATE TABLE LineItems (po_id INTEGER NOT NULL REFERENCES PurchaseOrders
    (po_id), item_id INTEGER NOT NULL, deliverydate DATETIME NULL,
    deliveryreference INTEGER NULL, invoicedate INTEGER NULL, invoiceref INTEGER
    NULL, PRIMARY KEY (po_id, item_id))

    First, this table appears not to be in Third Normal Form. You have both
    invoicedate, invoiceref, deliverydate and deliveryref in this table when
    logically you would expect just the two ref columns and then put the dates
    in an Invoice table and a Deliveries table. I presume you wouldn't expect
    different dates for the same invoice/delivery ref?

    Ignoring the design problem for the moment, the trigger you want would look
    something like this:

    CREATE TRIGGER trg_completed ON LineItems
    FOR UPDATE, INSERT, DELETE
    AS
    UPDATE PurchaseOrders
    SET complete =
    (SELECT MIN(CASE WHEN
    deliverydate IS NULL
    OR deliveryreference IS NULL
    OR invoicedate IS NULL
    OR invoiceref IS NULL THEN 0 ELSE 1 END)
    FROM LineItems
    WHERE po_id = PurchaseOrders.po_id)
    WHERE po_id IN
    (SELECT po_id FROM inserted)
    OR po_id IN
    (SELECT po_id FROM deleted)

    But, as I said in my previous post, a better, more relational design is to
    drop the Complete column altogether and create a view over the
    PurchaseOrders table:

    CREATE VIEW Orders_With_Completion_Status
    AS
    SELECT O.*, COALESCE(C.complete,0) AS complete
    FROM PucrhaseOrders AS O
    LEFT JOIN
    (SELECT po_id, 1 AS complete
    FROM LineItems
    GROUP BY po_id
    HAVING
    COUNT(deliverydate)=COUNT(*)
    AND COUNT(deliveryreference)=COUNT(*)
    AND COUNT(invoiceref)=COUNT(*)
    AND COUNT(invoicedate)=COUNT(*)) AS C
    ON O.po_id = C.po_id

    This has the advantage that the Completion status is always guaranteed to be
    up to date and it avoids the performance overhead of a trigger.

    Hope this helps.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  4. #4

    Default Re: newbie - trigger - syntax

    David,

    Thanks for the reply,

    As I said, the complete field is somewhat redundant but as I'm still trying
    to find my feet I don't want to confuse myself any more than I am at the
    minute. :)

    The reason why I haven't split normalised the invoice / delivery date
    information is because the Invoice will usually arrive after the actual
    delivery and thus are usually two different dates - although if I'm honest
    my normalisation technique (like everything else it seems) needs practice.

    However, thank you for your advice - I'll certainly follow up your
    suggestion once I've got the hang of triggers.

    On a more general note, I'd like to thank all those who have taken the time
    to answer what are my probably very silly questions.

    Thanks

    Chris S



    Chris Guest

Similar Threads

  1. pl/pgsql trigger: syntax error at or near "ELSEIF"
    By Roman Neuhauser in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: January 14th, 04:24 PM
  2. Newbie Question regarding Syntax
    By jkd in forum PHP Development
    Replies: 1
    Last Post: August 17th, 11:58 PM
  3. CREATE TRIGGER syntax
    By Ted in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 14th, 06:18 PM
  4. [newbie] syntax of a tar cmd
    By lulesque LUL in forum AIX
    Replies: 4
    Last Post: August 8th, 05:21 PM
  5. Newbie question - Trigger on Update
    By Nathan in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 7th, 09:17 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139