Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Answered: Trigger assistance

    Just for the sake of understanding.

    I have two fields one will be populated the other I wanted updated.

    The date field is in a date data type. 2015-12-31. I would like this column to after update, insert.

    Here is my script but it's bombing out. Any assistance would be greatly appreciated.

    Code:
    CREATE TRIGGER [dbo].[TG_METRIC_UPDATE_INSERT]
    ON [dbo].[Testing_TABLE_TRIGGER]
    AFTER INSERT, UPDATE
    AS
    
    SET NOCOUNT ON
    
    
    	UPDATE	[dbo].[Testing_TABLE_TRIGGER]
    	SET    2nd = 1st   ----Essentially I would like to take the date field 2015-12-31 = LEFT(1st, 7) to only pull the left 7 into a varchar field.  
    	FROM    [Testing_TABLE_TRIGGER]
    	JOIN    inserted as i ON Testing_TABLE_TRIGGER.UI = i.UI
    GO
    I've never created triggers before I really need to branch out. Anyway sorry random thoughts....
    Last edited by VLOOKUP; 06-23-15 at 15:05.

  2. Best Answer
    Posted by Pat Phelan

    "
    Code:
    CREATE TABLE vlookup_table (
       id			INT			IDENTITY
       PRIMARY KEY (id)
    ,  c1			VARCHAR(99)	NULL
    ,  c2			VARCHAR(99) NULL
       )
    GO
    
    CREATE TRIGGER vlookup_trigger_insert_update 
    ON vlookup_table
    AFTER INSERT, UPDATE
    AS
    
    SET NOCOUNT ON
    
    UPDATE thingie
       SET c2 = i.c1
       FROM vlookup_table AS thingie
       JOIN inserted AS i
          ON i.id = thingie.id
    GO
    
    INSERT INTO vlookup_table (c1)
       VALUES ('One'), ('Two'), ('OneTwo')
    
    SELECT *
       FROM vlookup_table
    
    DROP TRIGGER vlookup_trigger_insert_update
    GO
    
    DROP TABLE vlookup_table
    GO
    -PatP"


  3. #2
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Table is really basic (this one is for practice)

    Columns UI, 1st, 2nd
    Table Test_Trigger_Table

    Assume you'll put information in the 1st one this would be the date 2015-01-01 for example and the 2nd one would have a trigger fire off Left(column, 7)

    Just for reference.

  4. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE vlookup_table (
       id			INT			IDENTITY
       PRIMARY KEY (id)
    ,  c1			VARCHAR(99)	NULL
    ,  c2			VARCHAR(99) NULL
       )
    GO
    
    CREATE TRIGGER vlookup_trigger_insert_update 
    ON vlookup_table
    AFTER INSERT, UPDATE
    AS
    
    SET NOCOUNT ON
    
    UPDATE thingie
       SET c2 = i.c1
       FROM vlookup_table AS thingie
       JOIN inserted AS i
          ON i.id = thingie.id
    GO
    
    INSERT INTO vlookup_table (c1)
       VALUES ('One'), ('Two'), ('OneTwo')
    
    SELECT *
       FROM vlookup_table
    
    DROP TRIGGER vlookup_trigger_insert_update
    GO
    
    DROP TABLE vlookup_table
    GO
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Thanks for the follow up and answer.

    Pluralsight the best place to learn about more advanced SQL options such as Triggers?

    Pat - is the inserted a default table generated each time a trigger is created?

  6. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat I was STRUGGLING today and this helped so much. Thanks for chewing up my apple's and baby birding me the apple sauce.

    Just kidding!

  7. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The inserted object it technically a view with a dependency on the trigger. The inserted view (and the companion deleted view) only exists while the trigger is running, kind of like a temp table.

    Pluralsight is a great place to learn about everything, but triggers are only a tiny part of that everything. It would take over 609 years of continuous watching to see all of the content that is on PluralSight, but you could probably get through the SQL Server content in a decade or two!

    As a side note, triggers should be a "last ditch" tool that is only used after you've exhausted other choices. Triggers are quite "old school" and become more and more of a problem as you need to scale up database size and performance... Triggers can be useful, but they require a lot of care/thinking/planning and there are almost always better ways to achieve the same results.

    -PatP

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

  8. #7
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Here is why I used them.

    I have a metrics table, one table with the metric name and the other with the instances of that particular metric. I had a varchar field set with yyyy-mm, however in order to promote it to the BO Universe I had to remake the column with a date field. ***I was experiencing problems with control of the charting for the KPI dashboard I was building. In doing so the data comes over the -01 for the day. There is no way to really control that in BO or so I am told, so what I ended up doing is (SET i.Reporting_Text_Field = LEFT(Thingie.Reporting_Date_Field, 7)

    That way there is another dimension that comes through and I can leverage the text field which has more control.

  9. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In that case, I would prefer a Computed Column instead of using a Trigger. I'd use something like:
    Code:
    CREATE TABLE vlookup_computed (
       c1 CHAR(7)
       CONSTRAINT XCK01_vlookup_computed
          CHECK (1 = IsDate(c1 + '-01'))
    ,  c2 AS Cast(c1 + '-01' AS DATETIME)
    )
    
    SELECT * FROM vlookup_computed
    
    INSERT INTO vlookup_computed (c1)
       VALUES ('2000-01'), ('2002-03')
    
    SELECT * FROM vlookup_computed
    
    -- These two both fail the check constraint
    INSERT INTO vlookup_computed VALUES ('2000-00')
    INSERT INTO vlookup_computed VALUES ('2000-14')
    
    DROP TABLE vlookup_computed
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh crud... I just re-read your question and I might have solved it in reverse! If I did, try using:
    Code:
    CREATE TABLE vlookup_computed_the_other_direction (
       d1 DATETIME
    ,  c1 AS Convert(CHAR(7), d1, 121)
    )
    
    INSERT INTO vlookup_computed_the_other_direction (d1)
       SELECT DateAdd(m, -number, GetDate())
          FROM master.dbo.spt_values
    	  WHERE  'P' = type
    
    SELECT * FROM vlookup_computed_the_other_direction
    
    DROP TABLE vlookup_computed_the_other_direction
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    FWIW, if BO is SAP Business Objects, they're blowing smoke (and not from a good cigar either).

    Check the FormatDate() function!

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

  12. #11
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Yeah BO 4.1 WEBI

    Thanks again Pat as always I appreciate it.

    We have a project meeting tomorrow I'll bring it up.

Posting Permissions

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