Triggers with IF Statements - SQL Server Forums

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 with IF Statements

Author

Topic

tradingpassion
Starting Member

24 Posts

Posted - 2005-07-27 : 23:25:03

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"

Please do not answer if you dont know. That way you are not wasting your time and my time.

Thanks in advance.

jhermiz

3564 Posts

Posted - 2005-07-27 : 23:40:45

CREATE TRIGGER InsertDuplicateInTable2
ON dbo.Table1
FOR INSERT
AS

Declare @iField7 int

Set @iField7 = SELECT Field7 FROM Table1 WHERE ....

IF (@iField7 <> 0 )
BEGIN
insert into Table2
(Field1, Field2...
)

select
Field1, Field2...FROM Table1

End

???

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

tradingpassion
Starting Member

24 Posts

Posted - 2005-07-28 : 00:17:24

I am not sure how this will work?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-28 : 02:52:00

quote:
Originally posted by tradingpassion
Please do not answer if you dont know. That way you are not wasting your time and my time.

This pretty much assures you of getting a below average response to your request. Good luck.

-ec

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 02:57:43

You mean something like:

INSERT INTO table2
SELECT *
FROM inserted I
WHERE I.Field7 = 1

What do you want to ahve happen if the row already exists in table2?

Kristen

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-07-28 : 07:51:41

write a update trigger that will check for field7 updated, and if value = 1 then run an insert query and if other than 1 then dont run.

write a insert trigger that will check the value of field7 and if one then run an insert query.

tradingpassion
Starting Member

24 Posts

Posted - 2005-07-28 : 12:23:05

Kristen,

I already know how to do it if field7 is "1" but like you said if it already exists and say the value is "0" and 10 days later if I change it to 1 then I want to copy that row and paste it in Table2 as I mentioned above.NOTE-->At the same time I want to make sure it is also copying the rows if field7 is "1" the very first time I enter something in the row.
Does that make sense?

Thanks

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 12:37:49

You only want to insert it once? The very first time that Field7 becomes "1"?
If so would something like this in your trigger do?


INSERT INTO table2
SELECT *
FROM inserted I
WHERE I.Field7 = 1
      AND NOT EXISTS (SELECT * FROM table2 T2 WHERE T2.MyPKColumn = I.MyPKColumn)

Kristen

tradingpassion
Starting Member

24 Posts

Posted - 2005-07-28 : 21:41:26

Hello Jhermiz,

Can you please
elaborate on your reply?
Especially by "Set @iField7 = SELECT Field7 FROM Table1 WHERE ...."

Thanks

CREATE TRIGGER InsertDuplicateInTable2
ON dbo.Table1
FOR INSERT
AS

Declare @iField7 int

Set @iField7 = SELECT Field7 FROM Table1 WHERE ....

IF (@iField7 <> 0 )
BEGIN
insert into Table2
(Field1, Field2...
)

select
Field1, Field2...FROM Table1

End

???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources