STRANGE - TRIGGER EXECUTION? 1

[osjohnm]

Hi all

I have a FOR INSERT trigger on one table that inserts data into another table.

If the application does an insert into the base table the trigger executes.
If I execute a manual SQL insert statement, data is inserted into the base table but nothing happens in the other table.

At first I tried a bulk insert and then a one record insert and it still didn't work.

The login that the app uses has db_datareader and db_datawriter permissions, I have sysadmin rights.

Here is the trigger code but the trigger does work just not when I insert data thru query analyser.

CREATE trigger dbo.INSERT_TAGCOST on dbo.TAG_TRANSACTION
FOR INSERT
AS
-- fetch transaction entries from tag_transaction to record costs against the tag in tag_cost

insert into tag_cost (cost_class_id, tag_trx_id, tag_no, cost_type, cost_value, cost_desc, action_user, action_date, user_updated, date_updated)
select t.cost_class_id, t.tag_trx_id, t.tag_no, tc.cost_type, t.trx_value, t.trx_desc, s.sys_user_id, i.date_updated, t.user_updated, getdate()
from inserted i, tag_cost_class tc, ips2dev.dbo.sys_user s, tag_transaction t
where t.tag_trx_id = i.tag_trx_id
and t.user_updated = s.sys_user_nt_id
and t.cost_class_id = tc.cost_class_id
and (t.trx_type_code in ('Create') or t.cost_class_id in (1, 2, 4))
and (t.tag_trx_id not in (select tag_trx_id from tag_cost where tag_trx_id is not null))

Here is the insert statement:
insert into tag_transaction (tag_no, trx_type_code, trx_value, application, trx_desc, date_updated, cost_class_id)
select tag_no, 'IPMLOG', 150, 'IPM', 'Nominal Cost', getdate(), 4)
from asset where tag_no = 'PC8000'

trx_value uses money and cost_class_id use int therefore no ''.

What could the cause for this be?

John

 

[tlbroadbent]

I recommend the modifying the TRIGGER as in the following.

insert into tag_cost
(cost_class_id, tag_trx_id, tag_no, cost_type, cost_value,
cost_desc, action_user, action_date, user_updated,
date_updated)
select
--Use the values from the inserted table not tag_transaction
i.cost_class_id, i.tag_trx_id, i.tag_no, tc.cost_type,
i.trx_value, i.trx_desc, s.sys_user_id, i.date_updated,
i.user_updated, getdate()
from inserted i
--Use ANSI style joins
join tag_cost_class tc
On i.cost_class_id = tc.cost_class_id
join ips2dev.dbo.sys_user s
On i.user_updated = s.sys_user_nt_id
--Remove the tag_transaction table from the FROM clause.
--The inserted table contains the inserted data
Where (i.trx_type_code = 'Create'
Or i.cost_class_id in (1, 2, 4))
And (Not Exists
(select * from tag_cost
where tag_trx_id=i.tag_trx_id)) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[osjohnm]

Hi Terry

Thanks 4 the repsonse. Even changing the trigger didn't help. If data is inserted through the application the trigger executes. If any other means is used nothing happens.

I'm totally stumped. Here is all the tables etc, amybe there is something I'm missing.

Databases:
iData
iPS2Dev

Tables:
iData.dbo.Tag_Cost
iData.dbo.Tag_Transaction
iData.dbo.Tag_Cost_Class

iPS2Dev.dbo.Sys_User

Views:
iPS2Dev.dbo.Tag_cost
iPS2Dev.dbo.Tag_transaction
iPS2Dev.dbo.Tag_cost_class

The inserts take place on tag_transaction and the trigger has been created on tag_transaction. The trigger then inserts into tag_cost.

The application login has datareader and datawriter on both iData and iPS2Dev. The iData database stores central tables that are used by all applications eg. addressbook, location.

The applications share data and interact with each other and nothing like this has ever happended before.

I've look on the net but I haven't found an answer yet.

John

 

[rt63]

My guess is that the problem is in the join

and t.user_updated = s.sys_user_nt_id

Have you checked out whether the select statement in the trigger returns any rows. Where is t.user_updated getting populated (It is not part of the insert statement which is firing the trigger)?

RT

 

[tlbroadbent]

John,

Are you sure the trigger isn't firing? Perhaps, as RT suggested, the query is simply not selecting anything. RT asked about the user_updated column. Is the same login used for the application that works and Query Analyzer? When in QA are you using a system administrator login? If so, the database user will be DBO. In this case, is DBO in the ips2dev.dbo.sys_user table?

Try using a LEFT JOIN for the ips2dev.dbo.sys_user table. Then if no matching user is found, the row would still be inserted.

LEFT JOIN ips2dev.dbo.sys_user s



RT,

I would assume that user_updated has a default value of a system user function so it is updated automatically. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[osjohnm]

RT

I could kick myself! I was leaving the user_updated column out of the insert/select statement that I was executing.

Naturally the trigger fired because the application stores the user's domain account and that is what is stored in the user_updated field. THe app was inserting all the necessary data so the join criteria was met.

It was staring me in the face all along and I just didn't click.

Thanks 4 pointing out the error of my ways.

John

 

[osjohnm]

Terry

The user_updated column is inserted at run time. As I mentioned to RT the user info is your nt login account.
eg. MLUACCOUNT\OSJOHNM.

For example if you used the app and an did something to add data then the user_updated column would show DOMAIN\TLBROADBENT for that record.

My insert wasn't meeting the join criteria. Thanks 4 the tip to use a LFET JOIN. I'll add it in to the trigger and test it. This way if the user_updated column isn't inserted then the trigger should fire anyway.

Thanks
John