conditional trigger - 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)

conditional trigger

Author

Topic

Knot
Starting Member

3 Posts

Posted - 2008-02-23 : 11:54:55

Hi,

I have a situation where I'd like to fire a trigger only when certain conditions are met for data that's inserted into it's parent table.

Here is what I'm working with -

The PropertyScenarioIndex table currently contains this data:
PropertyID ScenarioType ScenarioID
1732 financing 1
1732 financing 2
1732 financing 3
1732 income_expense 1
1732 income_expense 2

The PropertyScenarioIndex table has a trigger on it (the one that needs to be made conditional) whose purpose is to automatically insert rows into another table called IncomeAndExpenseData to make entries easier for the user in that table. The primary keys in the PropertyScenarioIndex table are PropertyID, ScenarioType, ScenarioID and the primary keys in the IncomeExpenseData table are called PropertyID, ScenarioID, ItemID. Here is the trigger:


CREATE TRIGGER addstandardIncomeAndExpenserows ON PropertyScenarioIndex
AFTER INSERT
AS
insert into IncomeAndExpenseData
select
properytID = t1.propertyID,
scenarioID = t1.scenarioID,
itemID = t2.itemID,
itemvalue = null,
monthitemvalue = null
from PropertyScenarioIndex t1
cross join IncomeAndExpenseCodes t2
where
t1.propertyID+t1.scenarioID in (select propertyID+scenarioID from Inserted)
and t2.standarditem = 1

With the trigger as it is above, when the user tries to enter the following into PropertyScenarioIndex:
PropertyID ScenarioType ScenarioID
1732 income_expense 3
an error message appears that says Violation of PRIMARY KEY constraint 'PK_IncomeExpenseData'. Cannot insert duplicate key in object 'IncomeExpenseData'.

Is there some way to include code in the trigger that makes it fire only when the data inserted into the PropertyScenarioIndex table has a ScenarioType of 'income_expense'?

Note: in the where statement of the trigger above I have also tried this:


where
t1.propertyID+t1.scenarioID in (select propertyID+scenarioID from Inserted where scenariotype = 'income_expense')

but got the same error message as noted above.

Does anyone know how to build some code into the trigger that only allows the trigger to fire when the data inserted into PropertyScenarioIndex.scenariotype = 'income_expense'?

Thanks, Knot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-23 : 13:33:17

make where condition like this:-

where
t1.propertyID+t1.scenarioID in (select propertyID+scenarioID from Inserted)
and t2.standarditem = 1
and t1.ScenarioType='income_expense'

Knot
Starting Member

3 Posts

Posted - 2008-02-23 : 14:34:07

This seems to have half fixed the problem. After adjusting the where statement to include


and t1.ScenarioType='income_expense'

and then entering this data into the database:
PropertyID ScenarioType ScenarioID
1732 income_expense 3
it allowed it with no error messages. However, when I entered

PropertyID ScenarioType ScenarioID
1732 income_expense 4

and then tried to enter

PropertyID ScenarioType ScenarioID
1732 financing 4

I got the same error message as before upon trying to enter the financing scenario for propertyID 1732 scenarioID 4.

Is there some kind of code that can run a check on the ScenarioType field before it runs the insert standard rows script?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-23 : 14:36:14

>>t1.propertyID+t1.scenarioID in (select propertyID+scenarioID from Inserted)

What do you think that is doing? It is just doing basic math between two numbers and comparing the results of the two additions .. is that really what you want?

- Jeff
http://weblogs.sqlteam.com/JeffS

Knot
Starting Member

3 Posts

Posted - 2008-02-23 : 15:33:36

What I wanted this line:


where
t1.propertyID + t1.scenarioID in (select propertyID + scenarioID from Inserted)

to do is to limit the insertion of standard rows into the IncomeAndExpenseData table to only PropertyID and ScenarioID's that match the PropertyID and ScenarioID that was inserted into the PropertyScenarioIndex table. I don't want it to do the cross join and then insert standard rows for all PropertyID+ScenarioID's that exist in the PropertyScenarioIndex table.

Note that the data type of both the propertyID and the scenarioID field is nvarchar. The idea I was trying to use behind '+'ing them is so it compares them as though they make the record unique.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-23 : 15:53:25

where exists (select * from inserted as i where i.propertyid = t1.propertyid and i.scenarioid = t1.scenarioid)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources