January 21, 2013 at 12:26 am
there are two types of trigger for updating table,one is column level
and another is row level update trigger what is the difference?
January 21, 2013 at 1:07 am
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/
January 21, 2013 at 1:12 am
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;-)
January 21, 2013 at 7:42 am
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.
January 21, 2013 at 7:54 am
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
January 21, 2013 at 8:35 am
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/
January 22, 2013 at 12:14 am
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.....
January 22, 2013 at 1:28 am
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
January 22, 2013 at 7:11 am
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.
January 23, 2013 at 3:34 am
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