SQL Trigger

jnikodym
jnikodym used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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 WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Also, you have MySQL zone, but SQL Server 2005 tag - do you want to clarify ?
Bill BachPresident and Btrieve Guru

Commented:
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

Author

Commented:
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.

Author

Commented:
This is SQL Server 2005
Bill BachPresident and Btrieve Guru

Commented:
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.

Author

Commented:
Will this look at all the records in the table or only the new record that is added?
Bill BachPresident and Btrieve Guru

Commented:
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".

Author

Commented:
I get an error saying BEFORE is not a recognized trigger.
Bill BachPresident and Btrieve Guru

Commented:
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
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Sorry, it seemed a little bit contrived - sometimes these types of question are homework. Just wanted to make sure.

OK, one problem you do need to watch out for is recursion levels.

If your trigger fires on updates (and inserts) then you need to be careful that the update inside the trigger doesnt invoke itself yet again.

Here is a sample table and trigger with inserts. It uses TEMPDB creates the table, creates the trigger, inserts, and then deletes the table at the end

use tempdb
Go

create table my_ee_table (id int identity primary key, field1 int, field2 int)
Go

create trigger trg_my_ee_table on my_ee_table
after insert, update
as
begin

   IF TRIGGER_NESTLEVEL() > 1 RETURN

   set nocount on

   update my_ee_table set field2 = i.field1, field1 = 0
   from my_ee_table T
   inner join inserted I on I.id = T.id

end
Go

insert my_ee_table (field1) values (22)
Go

insert my_ee_table (field1)
select new_f1
from (select 1 as new_f1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 ) list
Go

Select * from my_ee_table
Go

drop table my_ee_table
Go

Open in new window

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 WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
@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


Author

Commented:
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 WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial