<div>
Is this a homework question ? Sounds a bit &quot;contrived&quot;<br />
<br />
If it is, no problems, we can help, but cannot answer for you.<br />
<br />
The basic steps are :<br />
<br />
1) create table<br />
2) create trigger - basically does an update of the table using the special temp table &quot;inserted&quot; to get the keys from<br />
3) insert values<br />
4) select from table to make sure it is populated correctly<br />
<br />
So, if you can show where you get up to and/or which bits cause the most problem, we can help you understand the next steps from there.
</div>


<div>
Also, you have MySQL zone, but SQL Server 2005 tag - do you want to clarify ?
</div>


<div>
This is an easy issue, as you just need to look at the NEW value. &nbsp;Write the CREATE TRIGGER as needed, but make sure that you have it run BEFORE the operation you are monitoring. &nbsp;Then, simply add :<br />
SET FIELDB = FIELDA, FIELDA = 0
</div>


<div>
Not sure what you mean by &quot;homework question&quot;. &nbsp;This is an actual database that i need one field's data replicated to another field each time a new record is added. &nbsp;I just need help with the correct syntax.
</div>


<div>
CREATE TRIGGER MyTrigger <br />
BEFORE UPDATE ON TableName<br />
FOR EACH ROW<br />
SET NEW.FieldB = NEW.FieldA, NEW.FieldA = 0<br />
<br />
Note, though, that two update statements in a row hitting the same row (even updating different fields) will blank the FieldB value.
</div>


<div>
Will this look at all the records in the table or only the new record that is added?
</div>


<div>
This trigger only looks at the newly updated records. &nbsp;If you want it to ONLY apply to newly INSERTED records, then change &quot;BEFORE UPDATE&quot; to &quot;BEFORE INSERT&quot;.
</div>


<div>
I get an error saying BEFORE is not a recognized trigger.
</div>


<div>
Forgot that Microsoft doesn't support BEFORE triggers. &nbsp;You'll need to use AFTER instead, and then change to a BEGIN/END Block and issue an appropriate SQL UPDATE statement, kind of like this:<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEGIN<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UPDATE TableName<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET FieldB = FieldA, FieldA = 0<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE PrimaryKeyField=PrimaryKey<wbr />Value<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END
</div>


<div>
SQL Server does not have a <b>BEFORE</b> trigger; however, it <u>does</u> have an <b>INSTEAD OF</b> trigger. &nbsp;Unfortunately, I am not at all sure about changing an <b>INSERT</b> into <b><u>one</u></b> table into an <b>INSERT</b> into <b><u><i>TWO</i></u></b> tables. &nbsp;<br />
<br />
I would recommend using a stored procedure instead of even bothering with an <i><b>AFTER</b></i> <b>INSERT</b> trigger. &nbsp;In the stored procedure, you can accept the data as parameters and then insert the 0 into Table A and the value (or values) into Table B (or any other tables you want to impact).
</div>


<div>
Oh, and in that test above, after the inserts, before the select, you can try the update as well e.g. <br />
<br />
<br />
update my_ee_table set field1 = 12345 where id &gt;&nbsp;5<br />
Go<br />
<br />
<br />
triggers are not so evil, you just need to be very careful as to when and where you need them.<br />
<br />

</div>


<div>
in your example above you have my_ee_table. &nbsp;That is your temporary table that you created correct? &nbsp;Where is the actual table in the database listed that the trigger will run on. &nbsp;I don't see the actual database table listed in the code.
</div>


<div>
The trigger is, in effect, hooked to the table for which it is a trigger. &nbsp;In other words, you don't specify the table that the trigger is <u>for</u> <u><i>in</i></u> the trigger; you just specify the actions to be taken.
</div>


<div>
@mark_wills,<br />
<br />
Admittedly, triggers are not <i>evil</i> but they can result in issues later on when someone changes a table just slightly and doesn't realize that there is a trigger somewhere that is associated with the table. &nbsp;Worse yet, if someone adds, ofr instance an INSERT or an UPDATE trigger on some other tables and there is a cascade effect that starts causing time-outs.<br />
<br />
With the advent of SQL Server 2005/2008/etc., the use of an OUTPUT clause can preclude the need for a trigger. &nbsp;However, since there was no mention of the specific <u>version</u> of SQL Server that is being targetted, that may not be an option.
</div>


<div>
@jnikodym,<br />
<br />
My example created a table and added a (DML) trigger to that table. It was by way of example.<br />
<br />
You would need to change that for your own table in your own database.<br />
<br />
If your table already exists, then you can add the trigger by using the CREATE TRIGGER command (or change it using ALTER TRIGGER).<br />
<br />
All the correct syntax is there for you to adapt. I suggest you run through the example first and use that as a guide before you start altering your live tables.<br />
<br />
Also, while you are most welcome to ask here, you can also use Books On Line to assist you with the code elements you dont quite understand e.g. <a href="http://msdn.microsoft.com/en-us/library/ms189799(v=sql.90).aspx" rel="ugc nofollow">http://msdn.microsoft.com/en-us/library/ms189799(v=sql.90).aspx</a><br />
<br />
<br />

</div>


<div>
would this work?<br />
<br />
SET ANSI_NULLS ON<br />
GO<br />
SET QUOTED_IDENTIFIER ON<br />
GO<br />
<br />
ALTER trigger [dbo].[NUMMACHINES] on [dbo].[WR010130]<br />
after insert<br />
as<br />
begin<br />
<br />
&nbsp; &nbsp;<br />
<br />
&nbsp; &nbsp;set nocount on<br />
<br />
&nbsp; &nbsp;update WR010130 set quantity_I = numberofmachines_I, numberofmachines_I = 0<br />
&nbsp; &nbsp;from WR010130<br />
&nbsp; &nbsp;<br />
end
</div>


<div>
Well, that is only on the initial insert. Which might be all you need.<br />
<br />
But you are updating the entire table (regardless of what was added). <br />
<br />
There is the very special temp table named INSERTED that has the inserted rows and you need to use that to make sure you are only affecting the rows being added. You can use the same type of update with the join to inserted as I used previously. <br />
<br />
Then, any subsequent updates to numberofmachines_I will be possible and it will not change quantity_I, which might be fine. <br />
<br />
The code will work, but does update every row. This is where you need to be careful and why some prefer to seek out alternate methods to using triggers. They can cause problems if not 100% and you need to be careful that it not only works (syntax), but only affects those rows that you need to affect.<br />

</div>


<div>
<div class="content wysiwyg-content">
I need to write a SQL trigger for a table. &nbsp;I need the trigger to look at the value in FIELD A and copy that value and move that value to FIELD B. &nbsp;I then need it to put a zero in FIELD A. &nbsp;So, if FIELD A started with a value of 100 my end result would be a value of 0 in FIELD A and a value of 100 in FIELD B. &nbsp;I need help with the correct syntax to make this happen.
</div>
</div>


I need to write a SQL trigger for a table.  I need the trigger to look at the value in FIELD A and copy that value and move that value to FIELD B.  I then need it to put a zero in FIELD A.  So, if FIELD A started with a value of 100 my end result would be a value of 0 in FIELD A and a value of 100 in FIELD B.  I need help with the correct syntax to make this happen.

SQL Trigger

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.