Results 1 to 4 of 4

Thread: Trigger in SQL Server

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2004
    Posts
    358

    Trigger in SQL Server

    Hi,

    I have a very simple table called PRODUCT. The table has 5 fields.

    proCode
    proDescription
    proPrice
    proCreationDate
    proModificationDate

    I have a VB.NET winform used to enter values in that table.

    But I would like to have a trigger taking care of the 2 date fields.

    When doing an INSERT I want to update the CreationDate and ModificationDate.
    When doing an UPDATE I want to update the ModificationDate.

    I tried this :

    Code:
    CREATE TRIGGER T_SET_DATE 
       ON  Products 
       AFTER INSERT
    AS 
    
    BEGIN
        UPDATE 
                   Products
        SET 
                   proCreationDate = GetDate(),
                   proModificationDate = GetDate()				
    
    END
    GO
    It works .. when inserting in the table .. but it updates the 2 fields for all the table records.

    How do I specify that I only want to modify the created record and not the others?

    Thanks for helping me on that.
    Telling me exactly how I should do the trigger for that specific use.

    I'm sure a lot of people use triggers to populate these 2 common fields in a table.

  2. #2
    Fanatic Member space_monkey's Avatar
    Join Date
    Apr 2005
    Location
    神と歩くこと
    Posts
    573

    Re: Trigger in SQL Server

    Well you need to specify which record you want to update, in your sql statement i believe something like this will work, but i'm not too sure about it.

    Code:
    CREATE TRIGGER T_SET_DATE 
       ON  Products 
       AFTER INSERT
    AS 
    
    BEGIN
        UPDATE 
                   Products
        SET 
                   proCreationDate = GetDate(),
                   proModificationDate = GetDate()				
        WHERE   
                   proCode = inserted.proCode
    END
    GO
    Using VB6 or VB.net 2008 with .net 3.5
    "Life... death... either way I'll be confined to a small cubicle!" - Hermes Conrad

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2004
    Posts
    358

    Re: Trigger in SQL Server

    Thanks.

    That gave me some pointers ..

    I don't know if it is a good way of doing it, but this code is giving me the results I need.

    Code:
    CREATE TRIGGER T_INSERT_DATE 
       ON  EtatCivil 
       FOR INSERT
    AS 
    BEGIN
    	
    		UPDATE 
    				EtatCivil 
    		SET 
    				ecDateCreation = GetDate(),
    				ecDateMAJ = GetDate()					
    		WHERE
    				idEtatCivil IN (SELECT idEtatCivil FROM INSERTED)
    END
    GO
    
    CREATE TRIGGER T_UPDATE_DATE 
       ON  EtatCivil 
       FOR UPDATE
    AS 
    BEGIN
    	
    		UPDATE 
    				EtatCivil 
    		SET 
    				ecDateMAJ = GetDate()					
    		WHERE
    				idEtatCivil IN (SELECT idEtatCivil FROM INSERTED)
    END
    GO

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,939

    Re: Trigger in SQL Server

    Unghfff!.... do it all in a store procedure.... don't waste time on trying to do something this simple in a trigger.... they are a PITA to debug... and heaven help you should something go wrong.

    -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??? *

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