Results 1 to 10 of 10

Thread: Need Help For SQL Trigger

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    5

    Need Help For SQL Trigger

    Dear all mmbers and VB professionals,
    =======================================
    i need help in SQL Trigger :
    i'm using VB 6.0 and MSSQL 2000
    =======================================
    Tables :
    1. Stock
    - code varchar(12) --->barcode
    - names varhcar(50)
    - qty int

    2. Sale
    - dos datetime --->date of sale
    - code varchar(12) --->barcode
    - qty int
    - Price money

    3. Purchase
    - dop datetime ---> date of purchased
    - code varchar(12) --->barcode
    - qty int
    - price money

    3a. Purchase_Log
    - dop datetime ---> date of purchased
    - code varchar(12) --->barcode
    - qty int
    - price money
    =======================================
    Conditions :
    * Purchase
    if code not found in stock then "Addnew" in stock table with a complete data (barcode,item name,qty)
    else
    if found then just update stock qty = qty + purchase.qty
    also INSERT INTO table "Purchase_Log"

    * Sale
    same condition
    =======================================
    Thank you very much for your help.
    =======================================

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Need Help For SQL Trigger

    Welcome to VBForums

    Thread moved to the 'Database Development' forum - which is where you should always post SQL based questions (while SQL can be used in VB6, it is certainly not specific to VB6)

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    5

    Re: Need Help For SQL Trigger

    thank you very much for helped me to move the thread...

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Need Help For SQL Trigger

    What's your question? You've given us detail about what you're trying to do and it seems quite standard stuff but I don't understand what you're actually asking us. What are you stuck on?
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    5

    Re: Need Help For SQL Trigger

    i'm sorry about my English ...
    i stuck on trigger insert update delete

    an example :
    i buy something and i input it from Purchase Form.
    normally for the existing item it will be updating field qty inside stock table

    i stuck here :
    when i input new item from input form, i want it automatically stored in stock table (as a new item)

    also need help on update and delete ...

    so far i never used trigger before ... just normal recordset and sql query ..

    Thank you ....

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Need Help For SQL Trigger

    This isn't a trigger situation... triggers should be avoided if they can... what you're describing is business logic, and so should be wrapped up in a stored procedure...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Need Help For SQL Trigger

    Are you after the basics of how to create a trigger? If so you just use the CreateTrigger command or you can do it through management studio: just right click on the 'Triggers' node of the table you want to add the trigger to and select 'New Trigger'.

    As TG said, though, triggers can be problematic. They tend to hide code, making it difficult to predict the results of an operation. They have their place but I agree with TG that this should be in a stored procedure or some other business logic layer.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    5

    Re: Need Help For SQL Trigger

    Stored Procedure ... ok ... thank all , i will try my best to figure it out ....
    but still, i need a support from you all, i know i can get a lot of knowledge in this forum and also can learn a lot from here .....

    Anyway ,. thx i'll be back after i figure it out .///

    Thank you

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Need Help For SQL Trigger

    @gr33nc0d3 - if you're wondering why a trigger isn't appropriate... if the data doesn't exist... there's no data to update, therefore there is no trigger that can be fired off...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    New Member
    Join Date
    Apr 2012
    Posts
    5

    Re: Need Help For SQL Trigger

    OK, thank you all ...

Posting Permissions

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



Click Here to Expand Forum to Full Width