SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


update trigger


update trigger

Author
Message
sej2008
sej2008
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 394
there are two types of trigger for updating table,one is column level
and another is row level update trigger what is the difference?
Adi Cohn
Adi Cohn
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8437 Visits: 6595
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/
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13714 Visits: 4077
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;-)
sej2008
sej2008
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 394
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231924 Visits: 46354
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


Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64561 Visits: 17978
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
sej2008
sej2008
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 394
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.....
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231924 Visits: 46354
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


toddasd
toddasd
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1360 Visits: 3820
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.
sej2008
sej2008
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 394
sorry in this case rollback after print statement, is necessary which I missed in the code.then it works perfect.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search