By default, a trigger is created in the enabled state. When a trigger fires, tables that the trigger references might be undergoing changes made by SQL statements in other users' transactions.
SQL statements running in triggers follow the same rules that standalone SQL statements do.
Col_2, Col_3 into Table_2 from Table_1 go select convert(int, Col_1) as Col_1, Col_2, Col_3 into Table_3 from Table_1 go -- Truncate 3 tables truncate table Table_1 truncate table Table_2 truncate table Table_3 go -- Clean SQL SERVER memory: dbcc dropcleanbuffers go -- Run 10000 inserts declare @i int set @i = 1 while @i -- Create one trigger with two inserts: create trigger trg_I_Table_1 ON Table_1 FOR INSERT as insert into Table_2 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3 from inserted insert into Table_3 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3 from inserted go -- Create two triggers – one with each command: create trigger trg1_I_Table_1 ON Table_1 FOR INSERT as insert into Table_2 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3 from inserted go create trigger trg2_I_Table_1 ON Table_1 FOR INSERT as insert into Table_3 (Col_1, Col_2, Col_3) select Col_1, Col_2, Col_3 from inserted go I ran the common script six times for each case -- three times cleaning SQL Server memory and three times without cleaning it -- and here are the results I received from SQL Profiler: The averages for CPU, reads, writes and duration were very close for all options.
There is no one "best solution." If you end up with a very long trigger, you should split it up it is easier to flush a smaller unit from memory as needed (or recompile it again when necessary).
create table t_update_before_each_row ( txt varchar2(10) ); create table log ( txt varchar2(20) ); create trigger update_before_each_row before update on t_update_before_each_row for each row begin ::= upper(:new.txt); insert into log values ('old: '
From the java end, It is possible to select the record by id and then update that record, but that would make 2 database calls.
Specifically: How Triggers and Constraints Differ Both triggers and constraints can constrain data input, but they differ significantly. For example, a trigger can prevent a DML statement from inserting a values that were inserted into the column before the trigger was defined or while the trigger was disabled.
A constraint can apply either to new data only (like a trigger) or to both new and existing data.
Otherwise, if you are not sure which solution is best in a specific case, just test it as I have demonstrated.
Explore Microsoft SQL Server triggers ABOUT THE AUTHOR: Michelle Gutzait works as a team member of the SQL Server Database Experts at a worldwide company providing Remote Administration services for databases, operating systems, SANs and networks.