Results 1 to 9 of 9
  1. #1
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Thumbs up Help with my first DML Trigger SQL Server 2008 R2

    Access 2007 with SQL SERVER 2008 R2. In design phase. I am developing triggers for all tables and once I have them running successfully, I will move them to ModelRight for SQL Server so when I generate the database design it auto-creates all of these generic triggers for all tables.

    Client requirement: All tables will contain the following fields 1) Creation Date, 2) Created By, 3) Modification Date, and 4) Modified By. These do NOT write to an Audit table. They exist only in the actual record created.

    This is the first time I have ever written a trigger. When a new record is inserted it should date stamp the CreatedDT field and enter the user name CreatedBY. Once entered the Creation Date and CreatedBY fields never change. (The next trigger will have Modified Date and Modified By which will change every time the record is updated but I have to get this one working first.) My thinking is that both pieces of data should be inserted by one trigger since they both should be inserted after Inserting a new record.

    I am finding most articles, videos, books to be fragmented in content, or demonstrating triggers with far more complicated processes then what I am trying to achieve. As a result they are not helpful to the beginner.

    I finished the code below. I open up the dbo.Property table and insert a record. I cannot commit the change to see if the Trigger works. I get the error below. If I chop this down and only run the Create Date trigger I can get it to work. But I can't get it to work together.

    My code is generating an error:

    Click image for larger version. 

Name:	Capture.JPG 
Views:	18 
Size:	27.3 KB 
ID:	14879

    I don't understand the error or what is wrong with my code. I don't have anything clear to follow so I may have multiple errors or issues.

    Code:
    CREATE TRIGGER DBO.TR_CreateDate
         On DBO.Property
         AFTER INSERT
    AS
    BEGIN
    
         SET NOCOUNT ON;
    
         Insert into Property(CreatedDT)
         values(GETDATE())
    
    END
    GO
    
    CREATE TRIGGER DBO.TR_CreateName
         On DBO.Property
         AFTER INSERT
    AS
    BEGIN
    
         SET NOCOUNT ON;
    
         Insert into Property(CreatedBy)
         values(SUSER_SNAME())
    
    END
    GO
    Remember I am a beginner on SQL SERVER and this is my first trigger.



    1. Can you include two actions in one trigger?
    2. Should they be separated as I have them?
    3. I am not sure SUSER_SNAME is correct or that the syntax is correct.
    4. If I execute the above trigger, before I try it on the table, it says that the Query executed successfully.
    5. Can one table have more than one trigger? Kind of the same question as 1.
    6. Should I write separate triggers for each action or combine them together with logic in between?
    7. Can anyone point me to clear examples of simple triggers like I am trying to create?


    Phred

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You don't want to "insert into"; you're creating an endless loop (inserting a record triggers another record to be inserted, which triggers...). You want to modify the record being inserted/updated. Only have a minute, but see if this helps:

    http://jbknet.blogspot.com/2010/03/s...o-created.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    Thanks Paul. I am trying it out and will probably have another questions. Thanks. Don't mark solved yet.

    Appreciate the help.

    Phred

  4. #4
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Data Type Mismatch I think

    I recreated the trigger you referred me to. I converted the field names to match my table. My CreatedDT is DateTime and my CreatedBY is varchar(50). Here is my code:

    Code:
    CREATE TRIGGER [trg_CreateModify]
    
    ON DBO.PROPERTY
    
    AFTER INSERT,UPDATE
    AS
    BEGIN
    SET NOCOUNT ON;
    
    Declare @now datetime
    Declare @user varchar(50)
    
    SELECT
    
    @now = GETDATE()
    ,@USER = USER_NAME()
    
    UPDATE T SET
    
    CretedDT = COALESCE (I.CreatedDT, @now)
    ,CretedBY = COALESCE (I.CreatedBY, @user)
    ,ModifiedDT = @now
    ,ModifiedBY = @user
    
    FROM inserted I
    JOIN dbo.Property as T
    on T.PropertyID = I.PropertyID
    
    End
    Go
    When I try to create a new record I get...

    Click image for larger version. 

Name:	Capture2.JPG 
Views:	15 
Size:	26.7 KB 
ID:	14887

    It sounds like a data type mismatch but I can't figure out where it is occurring.

    My field in SQL SERVER is DateTime and I am manually entering the data from SSMS dbo.Property directly into the field. So I am getting the error completely within SQL. Haven't even tried Access yet.

    Phred

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    On these two lines something is spelled wrong. Note the difference in the field names:

    CretedDT = COALESCE (I.CreatedDT, @now)
    ,CretedBY = COALESCE (I.CreatedBY, @user)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    My typo. I ended up retyping the code and mangled it in the code above. It is correct in the coed tested.

    CreatedDT = COALESCE

    CreatedBY = COALESCE

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, when I posted that I had made a test table with those field names and added that trigger. In a brief insert test, it fired and populated the fields. I'm not at the same location right now, so don't have access to the actual trigger I created.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246
    Hi Paul

    I figured it out. I was so focused on the CreatedDT, ModifiedDT, and my error, that I neglected to look further. In the field ModifiedBY I had inadvertently (read sloppy) identified it as "datetime". Uh Duh! Once I corrected it, the trigger worked fine.

    Thank you again.

    Fred

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem Fred. That would certainly cause a problem. Glad you sorted it out!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SQL Server Trigger Question
    By RayMilhon in forum SQL Server
    Replies: 4
    Last Post: 04-19-2013, 11:35 AM
  2. Replies: 0
    Last Post: 12-20-2012, 01:44 PM
  3. 2008 Server R2
    By Randy in forum Access
    Replies: 0
    Last Post: 03-15-2012, 01:24 PM
  4. Server 2008 RC
    By Randy in forum Access
    Replies: 3
    Last Post: 01-20-2012, 11:49 PM
  5. SQL Server 2008 R2 Replication
    By RayMilhon in forum SQL Server
    Replies: 0
    Last Post: 11-17-2011, 10:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums