trigger firing but updates wrong values..

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

General SQL Server Forums

New to SQL Server Programming

trigger firing but updates wrong values..

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-08-19 : 16:25:41

i have a column in table1 name "neworganisation" with no values. I want to update the values in the column on the basis of other four columns (qt1, qty2, qty3, qt4) automatically when new data is imported.

I wrote the following trigger for that.
[CODE]set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [insqm] on [dbo].[table1]
AFTER INSERT
AS
BEGIN
Declare @neworganisation as varchar(50)
set @neworganisation = (select case when qty1 <> 0 Then 'abc'
when qty2 <> 0 Then 'xyz'
when qty3 <> 0 Then 'mno'
when qty4 <> 0 Then 'def'
else '0' end from inserted i)

update table1 set neworganisation = @neworganisation

END[/CODE]
Though trigger is firing, it puts whole neworganisation column with values "abc".

But if I verified the code separately in a new query window using select statement, it displays correct output. So I think there is something wrong with the trigger.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-19 : 16:49:37

What you want is something like this - but I don't know what the Primary KeyColumn in your table is.

UPDATE t1 SET
	neworganisation = 
	CASE
		WHEN COALESCE(i.qty1,0) <> 0 THEN 'abc'
		WHEN COALESCE(i.qty2,0) <> 0 THEN 'xyz'
		WHEN COALESCE(i.qty3,0) <> 0 THEN 'mno'
		WHEN COALESCE(i.qty4,0) <> 0 THEN 'def'
		ELSE '0'
	END 
FROM
	table1 t1
	INNER JOIN INSERTED i ON
		i.PrimaryKeyColumn = t1.PrimaryKeyColumn;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-19 : 16:50:00

Coding triggers for row by row processing will bring you some troubles. Please read this: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Your trigger is also updating the entire table and not just the row that got inserted.

Try this:


update t1 
set neworganisation = case when i.qty1 <> 0 Then 'abc'
					when i.qty2 <> 0 Then 'xyz'
					when i.qty3 <> 0 Then 'mno'
					when i.qty4 <> 0 Then 'def' 
                        else '0' end 
from table1 t1
join inserted i on t1.YourPkColumn = i.YourPkColumn

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-19 : 16:50:30

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-08-20 : 03:23:43

Thanks Sunitabeck and tkzier.
I don't have any primary column in the table. Then how I can make it work??
Do you think if I add an id column with identity(1,1) and make it as a primary key, will work the trigger?

@tkzier...I want to update entire table only once but next time when i import data I want to update only inserted records. Otherwise I guess it will take lot of time.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 06:52:01

It does not need to be a primary key column. If you have any candidate keys - i.e., a column or a combination of columns that is unique for each row, that would suffice.

If you don't have any such column(s) in the table, then you would need to add something such as an identity column. However, if there are no candidate keys, that can be an indication of something not quite right with the design. Also, for a variety of reasons, experts recommend that in all but exceptional cases, a table be created with a primary key.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-20 : 11:00:40

I want to update entire table only once but next time when i import data I want to update only inserted records. Otherwise I guess it will take lot of time.

do you mean incremental processing? do you've an audit field like datecreated in your table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-08-20 : 15:54:39

@sunitabeck....i don't have any column which is unique so I created an id column and used it as below. Actually I wanted to update one more column and exact my code is below


Declare @neworganisation as varchar(50), @oldorganisation as varchar(50)
set @neworganisation = (select case	when qty1 <> 0 Then 'abc'
					when qty2 <> 0 Then 'xyz'
					when qty3 <> 0 Then 'mno'
					when qty4 <> 0 Then 'def' 
                        else '0' end from table1)

set @oldorganisation = (select case	when qty1 <> 0 and type = 'a' Then 'oldabc'
					when qty2 <> 0 and type = 'b' Then 'oldxyz'
					when qty3 <> 0 and type = 'c' Then 'oldmno'
					when qty4 <> 0 and type = 'd' Then 'olddef' 
                        else '0' end from table1)

update table1 set neworganisation = @neworganisation,
               set oldorgansiation = @oldorganisation from table1
inner join inserted i on i.idcolumn = table1.idcolumn

i don't know how to execute trigger manually. So to test if trigger works, i imported new data into the table, but I got error something like Subquery returned more than one value

@visakh16...i don't have any such columns. neworganisation and oldorganisation are just calculated columns which I want to calculate automatically when new data is imported into it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-08-20 : 16:05:05

Do not use variables! Take a look at the code we posted. It handles sets. Your code handles one row.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-20 : 21:04:24

To add to what Tara said, if you want to update two columns, you can do both in a single update statement - just separate them with a comma as in:

UPDATE t1 SET
	neworganisation =
	CASE 
		WHEN i.qty1 <> 0 THEN 'abc'
		WHEN i.qty2 <> 0 THEN 'xyz'
		WHEN i.qty3 <> 0 THEN 'mno'
		WHEN i.qty4 <> 0 THEN 'def' 
		ELSE '0' 
	END,
	oldorgansiation = 
	CASE 
	     WHEN i.qty1 <> 0 AND i.type = 'a' THEN 'oldabc'
	     WHEN i.qty2 <> 0 AND i.type = 'b' THEN 'oldxyz'
	     WHEN i.qty3 <> 0 AND i.type = 'c' THEN 'oldmno'
	     WHEN i.qty4 <> 0 AND i.type = 'd' THEN 'olddef'
	     ELSE '0'
	END
FROM
	table1 t1
	INNER JOIN INSERTED i ON
		i.idcolumn = t1.idcolumn;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources