update trigger

  • there are two types of trigger for updating table,one is column level

    and another is row level update trigger what is the difference?

  • There is no column level or row level trigger. A trigger is defined according to the operation on the table, so you can have triggers for insert, update and delete. The trigger is fired only once and won't be fired for each row that was affected nor for each column. That means that if I issued an update statement on a table that has an update trigger, and the statement updated 4 columns in 20 rows, the trigger will run only once. If it is important for me to base trigger's operation on the columns that were modified, it is up to me to check in the trigger's code which column was modified.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • sej2008 (1/21/2013)


    and another is row level update trigger

    from BOL

    sql_statement

    Is the trigger condition(s) and action(s). Trigger conditions specify additional criteria that determine whether the attempted DELETE, INSERT, or UPDATE statements cause the trigger action(s) to be carried out.

    The trigger actions specified in the Transact-SQL statements go into effect when the DELETE, INSERT, or UPDATE operation is attempted.

    sej2008 (1/21/2013)


    there are two types of trigger for updating table,one is column level

    from BOL

    IF UPDATE (column)

    Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations. More than one column can be specified. Because the table name is specified in the ON clause, do not include the table name before the column name in an IF UPDATE clause. To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. IF UPDATE will return the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

    for details .See this link http://msdn.microsoft.com/en-us/library/aa258254(v=sql.80).aspx

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • ok now my code which is not allowing salary coulmn in my emptable

    to get updated

    create trigger tgigupdate

    on emptable

    for update

    as

    if update(salary)

    begin

    print 'can not update salary '

    end

    go

    it is working fine as I do not want to allow any updates of salary col.

    here it does not allow any single row update and also multiple row update s to salary column.what according to you should happen is this correct?

    pls suggest and explain if it is wrong.

  • As it stands, it's not going to prevent anything. It just notes that updates aren't permitted and then finishes, thus completing the update.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Also your script does not take into account if the current and updating values are the same. If you prevent updating the column like you have in your trigger it will not allow the update even if the value is the same.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Once again to bother you,but as per my code which is working fine as per my requirement ,it is an example from good sql book,there was an argument that it should allow at least one raw to update and not multiple updates in salary column which I do not agree and I do not think that there is anything wrong in it.what u have to say.....

  • I say exactly what I said earlier.

    As it stands, it's not going to prevent anything. It just notes that updates aren't permitted and then finishes, thus completing the update.

    In SQL Server, triggers fire whether there's been a single change or multiple changes. There are no row triggers (that's Oracle iirc)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sej2008 (1/22/2013)


    Once again to bother you,but as per my code which is working fine as per my requirement ,it is an example from good sql book,there was an argument that it should allow at least one raw to update and not multiple updates in salary column which I do not agree and I do not think that there is anything wrong in it.what u have to say.....

    create table emptable (salary int);

    insert into emptable values (10), (20), (30)

    select * from emptable;

    go

    create trigger tgigupdate

    on emptable

    for update

    as

    if update(salary)

    begin

    print 'can not update salary '

    end

    go

    update emptable set salary = salary + 1;

    select * from emptable; --the print statement is issued, all values are updated

    drop table emptable;

    So your requirement says to output 'can not update salary' but update them anyway?

    ______________________________________________________________________________
    How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

  • sorry in this case rollback after print statement, is necessary which I missed in the code.then it works perfect.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply