TRIGGERS for updating/inserting a value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

TRIGGERS for updating/inserting a value

Author

Topic

tradingpassion
Starting Member

24 Posts

Posted - 2005-07-28 : 22:42:57

I know how to create basic trigger but not how to Create Triggers with If Statements
Here is what I am trying to do :

I have an existing table..let's say "Table1"
I want to create trigger on "Table1" so that whenever there is a new entry added it triggers and copies that row to the new table "Table2"
In "Table1" there is a field "Field7" and the values are either "0" or "1".
When you are adding a new row the value can be either "0" or "1" IN "FIELD7". So whenever somebody enters "1" for "Field7" I want to Trigger and make sure the values gets copied in "Table2". Easy enough and I know how to do that.
Now what I want to know is if somebody adds "0" then I do not want to copy the row in "Table2" BUT IF SOMEONE CHANGES THAT "0" TO "1" 10, 15 OR 20 DAYS LATER THEN I WANT THE TRIGGER TO COPY THE ROW IN "TABLE2" BUT I M NOT SURE HOW TO DO THAT???????????
NOTE AT THE SAME TIME I WANT TRIGGER TO ALWAYS COPY THE ROW IF IT'S "1"

Thanks in advance.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-29 : 00:25:48

create an after update,insert trigger on source table,

here's something to start with

insert into destination(fields...)
select(fields...) from inserted
where field7>0

HTH
--edit, well this is the same post here, Kristen gave you the logical answer, you could probably elaborate more on what you really require

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52926

--------------------
keeping it simple...

giovi2002
Starting Member

46 Posts

Posted - 2005-07-31 : 16:46:12

In the beginning of your trigger you can use if with update statement .
Take a look at this one, i defined a trigger which should be fired when field7 is being updated, i copied the structure of a working trigger and replaced some field names to match yours:

CREATE TRIGGER UPDATEFIELD7 ON dbo.mytable
AFTER UPDATE
AS
If Update(field7)
Begin
....
End

Between Begin and end you can place your statements. The trigger will only be fired on a change of field 7. T-SQL uses the update([namefield]) statement to decide whether your field is being updated

tradingpassion
Starting Member

24 Posts

Posted - 2005-07-31 : 20:26:06

Thanks giovi2002!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources