SQL Server Performance

Need trigger to modify current row only

Discussion in 'General Developer Questions' started by dvarozza, Jul 27, 2005.

  1. dvarozza New Member

    I have a table named CounterData as:

    CounterID (int)
    CounterValue (int)
    CounterDateTime (char 24)

    I am trying to write a trigger that will modify the CounterDateTime value for each row as it is inserted into the table. Here is my Trigger as it is working now with the problem/questions to follow:

    ***
    CREATE TRIGGER [Trigger1] ON [dbo].[CounterData]
    FOR INSERT, UPDATE
    AS
    UPDATE CounterData
    SET CounterDateTime = CAST(counterdatetime as datetime)
    ***

    Problems:
    1.) This trigger method is actually doing the CAST operation on every record in the CounterDateTime column over and over for every time there is an UPDATE or INSERT. I need to make the trigger only manipulate the current record each time ther is an INSERT or UPDATE --- NOT the entire column over again each time.

    2.) This trigger does not seem to work when the records are inserted by the application. However if I manually go into the CounterData table and manually update one of the records, the trigger kicks in and corrects the entire CounterDateTime column.

    Essentially, I need the trigger to only work with the current record as it is inserted or updated. How can I do this?

  2. merrillaldrich New Member

    You just need a WHERE clause in the update:

    UPDATE CounterData
    SET CounterDateTime = CAST(counterdatetime as datetime)
    WHERE [some condition that identifies the specific row(s) that need updating]

    Can I ask why this operation is in a trigger? It seems a little unorthodox to recast data in the same column using a trigger.
  3. dvarozza New Member

    Thanks for the reply.

    1.) The reason I am recasting data in the same column is that the data is being entered into the table from Windows Perfmon Counter Logs which to my knowledge there is no way to change the format that Perfmon supplies the data in. So, Perfmon is inserting data into the CounterDateTime column in a char(24) format with a bracket character at the end. This format does not let me use datetime comparisons later on in my quieries so I am trying to get the data entered into the table in a casted format that I can use.

    2.) As for using the where clause.... Your example says to use Where with [some condition that identifies a specific row]. You are correct, that is exactly what I need to do BUT.... the specific row in this case will always be the specific row that is currently being inserted at any given time. That is my problem - how do I tell sql to only update the CURRENT ROW and not the entire column each time the trigger is run?

    Hope this makes sense - I am a newbie.
  4. merrillaldrich New Member

    Oh, I see.

    Triggers can operate on the tables "inserted" and "deleted," which are special temporary tables that hold your new or changing rows as an insert or update or delete is processed. Check out BOL under "Using the inserted and deleted Tables." You can use those to isolate only the new rows.
  5. ghemant Moderator

    for example i have a table tableA which structures<br />create table table1 (fname varchar(20) not null,ndate char(20) not null) <br />and it has a values 'Hemant',currentdate and 'goswami',currentdate<br />if have to update a value from 'hemant' to 'hemantgiri' and and that time i wants to update the record and trace them using trigger and "Magic Tables : inserted / deleted "<br /><br />-- for insert because inserted table stores a data to be inserted <br />create trigger trig1 <br />on dbo.table1<br />for insert <br />as<br />begin<br />insert into dbo.table2 select *.inserted from deleted where CONVERT(char(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, generation_date, 112) = ndate<br /><br />end<br /><br /><br />HTH<br /><br /><br />hsGoswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami<br />
  6. FrankKalis Moderator

    You need to join to the inserted table


    UPDATE Table1
    SET column = CAST(column AS DATETIME)
    FROM Table1
    JOIN inserted
    ON Table1.cID=Inserted.ciD


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Share This Page