Avatar of jnikodym
jnikodym
 asked on

SQL Trigger

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.
MySQL Server

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon
Mark Wills

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.
Mark Wills

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

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
jnikodym

ASKER
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.
jnikodym

ASKER
This is SQL Server 2005
Bill Bach

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jnikodym

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

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".
jnikodym

ASKER
I get an error saying BEFORE is not a recognized trigger.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Bill Bach

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
ASKER CERTIFIED SOLUTION
Mark Wills

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
8080_Diver

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).
Mark Wills

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.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jnikodym

ASKER
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.
8080_Diver

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.
8080_Diver

@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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Mark Wills

@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


jnikodym

ASKER
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
Mark Wills

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.