SQL Trigger

Is this a homework question ? Sounds a bit "contrived"

If it is, no problems, we can help, but cannot answer for you.

The basic steps are :

1) create table
2) create trigger - basically does an update of the table using the special temp table "inserted" to get the keys from
3) insert values
4) select from table to make sure it is populated correctly

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.

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

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

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

This is SQL Server 2005

CREATE TRIGGER MyTrigger
BEFORE UPDATE ON TableName
FOR EACH ROW
SET NEW.FieldB = NEW.FieldA, NEW.FieldA = 0

Note, though, that two update statements in a row hitting the same row (even updating different fields) will blank the FieldB value.

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

This trigger only looks at the newly updated records.  If you want it to ONLY apply to newly INSERTED records, then change "BEFORE UPDATE" to "BEFORE INSERT".

I get an error saying BEFORE is not a recognized trigger.

Forgot that Microsoft doesn't support BEFORE triggers.  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:
                BEGIN
                    UPDATE TableName
                        SET FieldB = FieldA, FieldA = 0
                    WHERE PrimaryKeyField=PrimaryKeyValue
                END

SQL Server does not have a BEFORE trigger; however, it does have an INSTEAD OF trigger.  Unfortunately, I am not at all sure about changing an INSERT into one table into an INSERT into TWO tables.  

I would recommend using a stored procedure instead of even bothering with an AFTER INSERT trigger.  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).

Oh, and in that test above, after the inserts, before the select, you can try the update as well e.g.


update my_ee_table set field1 = 12345 where id > 5
Go


triggers are not so evil, you just need to be very careful as to when and where you need them.

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

The trigger is, in effect, hooked to the table for which it is a trigger.  In other words, you don't specify the table that the trigger is for in the trigger; you just specify the actions to be taken.

@mark_wills,

Admittedly, triggers are not evil 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.  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.

With the advent of SQL Server 2005/2008/etc., the use of an OUTPUT clause can preclude the need for a trigger.  However, since there was no mention of the specific version of SQL Server that is being targetted, that may not be an option.

@jnikodym,

My example created a table and added a (DML) trigger to that table. It was by way of example.

You would need to change that for your own table in your own database.

If your table already exists, then you can add the trigger by using the CREATE TRIGGER command (or change it using ALTER TRIGGER).

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.

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. http://msdn.microsoft.com/en-us/library/ms189799(v=sql.90).aspx

would this work?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER trigger [dbo].[NUMMACHINES] on [dbo].[WR010130]
after insert
as
begin

   

   set nocount on

   update WR010130 set quantity_I = numberofmachines_I, numberofmachines_I = 0
   from WR010130
   
end

Well, that is only on the initial insert. Which might be all you need.

But you are updating the entire table (regardless of what was added).

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.

Then, any subsequent updates to numberofmachines_I will be possible and it will not change quantity_I, which might be fine.

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.