Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

update trigger Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 12:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 23, 2014 7:58 AM
Points: 115, Visits: 210
there are two types of trigger for updating table,one is column level
and another is row level update trigger what is the difference?
Post #1409374
Posted Monday, January 21, 2013 1:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
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/
Post #1409391
Posted Monday, January 21, 2013 1:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950

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
Post #1409393
Posted Monday, January 21, 2013 7:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 23, 2014 7:58 AM
Points: 115, Visits: 210
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.

Post #1409556
Posted Monday, January 21, 2013 7:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
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 2008, MVP
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

Post #1409565
Posted Monday, January 21, 2013 8:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:52 PM
Points: 13,069, Visits: 11,907
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 Moden's 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)
Post #1409583
Posted Tuesday, January 22, 2013 12:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 23, 2014 7:58 AM
Points: 115, Visits: 210
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.....
Post #1409813
Posted Tuesday, January 22, 2013 1:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
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 2008, MVP
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

Post #1409856
Posted Tuesday, January 22, 2013 7:11 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 11, 2013 3:13 PM
Points: 477, Visits: 3,671
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.
Post #1410022
Posted Wednesday, January 23, 2013 3:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 23, 2014 7:58 AM
Points: 115, Visits: 210
sorry in this case rollback after print statement, is necessary which I missed in the code.then it works perfect.
Post #1410443
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse