Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: Stored Procedure and Trigger

    Hi

    I am trying to:

    Setup a Trigger on Table1 which, when record Insert is done, will call a Stored Procedure (this will write some details to Table2).

    I need to know:

    1. The Stored Procedure - How to get the Primary Key (from Table1) and write this to Table2. Not sure about @@IDENTITY and SCOPE_IDENTITY().

    2. The Storedprocedure - How to get the current date to write to Table2. I think I can use the date on the server for this, but don't know how to get it.

    3. The Trigger - How to call the Storedprocedure from the Trigger.

    Can anyone please help with all, or some of these?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why call a sproc from your trigger - why not just put the sproc code in the trigger?
    Code:
    CREATE TRIGGER dbo.meh
      ON dbo.table1
      FOR INSERT
    AS
      BEGIN
        INSERT INTO dbo.table2 (id, some_field, date_created)
        SELECT id
             , some_field
             , GetDate()
        FROM   inserted
      END
    GO
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Good question!

    I had already started the Stored Procedure and read some where that you should put minimum code in Triggers, so decided to do it with a call. Will try it that way - not sure of code, as new to Sql. How does the GETDATE() work if I want to put current date into a field and write it to Table2?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT GetDate() As current_datetime
         , current_timestamp As another_method
    
    WAITFOR DELAY '00:00:10'
    
    SELECT GetDate() As ten_seconds_later
         , current_timestamp As ten_seconds_later
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First order of business, the concept behind putting as little code into triggers as possible is intended to limit how much work that triggers do, not how many lines of code goes into them. As a general rule, triggers should be used only as a "last resort" when there is no other way to accomplish what you need to do, and a rewrite should be considered long before expanding triggers. Triggers should very, very rarely call or execute anything.

    With that said, there are some things that triggers can do and do well, that there isn't another good way to do with SQL Server. Examples of what triggers are especially good for include recording the user/date/time of last modification for rows which is a simple UPDATE.

    General rules I use for triggers:
    1) If it needs a COMMIT or a ROLLBACK, it doesn't belong in a trigger!
    2) Never do ANYTHING that can be done outside the trigger.
    3) Never call anything that you don't absolutely need to call. No stored procedures, no UDFs, and no code external to SQL server.

    Ignoring the code you currently plan to use, can you describe in English what you want to do with your trigger? I'm trying to understand what you want to help you find a way to accomplish that in a way which won't hurt you badly later!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A trigger is as close to the data as code can get. Avoid referencing external code in triggers if at all possible. Duplicate the business logic in the trigger, if necessary.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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