Update Trigger Issue - 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 2005 Forums

Transact-SQL (2005)

Update Trigger Issue

Author

Topic

Shilpa22
Starting Member

37 Posts

Posted - 2010-08-18 : 02:43:33

Hello Frnds,

I have 1 table in which a Job updates a STATUS col with(6000,9000) based on the Datasource column for AccountID. As soon as the value is updated in the Table 1 I want to decrement an INT col with 1 value for tht Account.
How can I achieve this. Please help me out.

THanks in Advance.

Thanks in Advance
Shilpa

Shilpa22
Starting Member

37 Posts

Posted - 2010-08-18 : 03:37:12

I am trying with the below 1, but getting some error
CREATE TRIGGER afterupdate_JobQuery ON DWS..DWS_Job_Query for update
if update(query_status)
update dws..DWS_AccountStatus set Req_Count = Req_Count - 1 where account_id = inserted.account_id and data_source_id=inserted.Data_Source_ID
Go

Error:Incorrect syntax near the keyword 'if'

Please help me out.

Thanks in Advance
Shilpa

manub22
Starting Member

6 Posts

Posted - 2010-08-18 : 03:55:25

Modify your code below:

CREATE TRIGGER afterupdate_JobQuery ON DWS..DWS_Job_Query for update
as

declare @account_id int
declare @Data_Source_id int
select @account_id = account_id from inserted
select @Data_Source_ID = Data_Source_ID from inserted

if update(STATUS)

update dws..DWS_AccountStatus
set Req_Count = Req_Count - 1
where account_id = @account_id
and data_source_id = @Data_Source_ID

Go

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-18 : 10:13:35

quote:
Originally posted by manub22

Modify your code below:

CREATE TRIGGER afterupdate_JobQuery ON DWS..DWS_Job_Query for update
as

declare @account_id int
declare @Data_Source_id int
select @account_id = account_id from inserted
select @Data_Source_ID = Data_Source_ID from inserted

if update(STATUS)

update dws..DWS_AccountStatus
set Req_Count = Req_Count - 1
where account_id = @account_id
and data_source_id = @Data_Source_ID

Go

The above wont work if multiple records are updated at a time.Use like..

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER afterupdate_JobQuery ON DWS..DWS_Job_Query 
FOR UPDATE
AS
BEGIN

	IF UPDATE(query_status)
	BEGIN
		UPDATE A
			SET A.Req_Count  = A.Req_Count - 1
		FROM dws..DWS_AccountStatus  A
		INNER JOIN Inserted B ON A.account_id = B.account_id and A.data_source_id=B.Data_Source_ID
	END
END

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-18 : 11:01:26

ya know...

The DDL of the Table would help, along with sample data, the actual code that does the update, and what you want the data to look like after

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Shilpa22
Starting Member

37 Posts

Posted - 2010-08-20 : 17:06:21

Thanks for your help..
I followed your sugestions...but still the records are not getting updated.
pls help

Thanks in Advance
Shilpa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-20 : 17:22:06

You can't do sequential substraction like that! And yes, your table is updated, not just with the result you want.
If you want us to help you, please be more precise. "Not getting updated" means, for us, that no value changed at all.

See here in this example how your thinking works in real life.

DECLARE	@Source TABLE
	(
		x INT,
		i INT
	)

INSERT	@Source
VALUES	(1, 10)

SELECT	*
FROM	@Source

DECLARE	@Control TABLE
	(
		x INT
	)

INSERT	@Control
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	1 UNION ALL
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	1 UNION ALL
SELECT	1

UPDATE		s
SET		s.i = s.i - 1
FROM		@Source AS s
INNER JOIN	@Control AS c ON c.x = s.x

-- You would expect 5 right? You will get 9 instead.
-- Why? Because you still think RBAR instead of set-based.
-- The reason is that the JOIN will produce a match, but the
-- substraction is done per statement, not per matching rows in the join.
SELECT	*
FROM	@Source

This is how you must proceed with your trigger code!

CREATE TRIGGER	AfterUpdate_JobQuery
ON		DWS..DWS_Job_Query
AFTER		UPDATE
AS

SET NOCOUNT ON

IF UPDATE(Query_Status)
	UPDATE		accstat
	SET		accstat.Req_Count = accstat.Req_Count - i.cnt
	FROM		dws..DWS_AccountStatus AS accstat
	INNER JOIN	(
				SELECT		Account_ID,
						Data_Source_ID,
						COUNT(*) AS cnt
				FROM		inserted
				GROUP BY	Account_ID,
						Data_Source_ID
			) AS i ON i.Account_ID = accstat.Account_ID
				AND i.Data_Source_ID = accstat.Data_Source_ID
GO

N 56°04'39.26"
E 12°55'05.63"

Shilpa22
Starting Member

37 Posts

Posted - 2010-08-21 : 08:55:56

Hi,
I used the same above trigger, still the values are not changing in the second Table. Not getting any clue..how to resolve this.
Please help me

Thanks in Advance
Shilpa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-21 : 10:12:24

Post all relevant code. You must be missing something in your copy and paste operation.

N 56°04'39.26"
E 12°55'05.63"

Shilpa22
Starting Member

37 Posts

Posted - 2010-08-21 : 11:24:08

Below is the one which I am trying...
alter TRIGGER AfterUpdate_JobQuery
ON DWS..DWS_Job_Query
AFTER UPDATE
AS
SET NOCOUNT ON
IF UPDATE(Query_Status)
UPDATE accstat
SET accstat.Req_Count = accstat.Req_Count - i.cnt
FROM dws..DWS_AccountStatus AS accstat
INNER JOIN (
SELECT Account_ID,
Data_Source_ID,
COUNT(*) AS cnt
FROM inserted
GROUP BY Account_ID,
Data_Source_ID
) AS i ON i.Account_ID = accstat.Account_ID
AND i.Data_Source_ID = accstat.Data_Source_ID
GO

Thanks in Advance
Shilpa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 13:19:09

[code]
alter TRIGGER AfterUpdate_JobQuery
ON DWS..DWS_Job_Query
AFTER UPDATE
AS
SET NOCOUNT ON
IF EXISTS(SELECT 1
FROM INSERTED i
JOIN DELETED d
ON d.Account_ID = i.Account_ID
AND i.Data_Source_ID = d.Data_Source_ID
AND d.Query_Status <> i.Query_Status
AND i.Query_Status IN (9000,6000)
)
BEGIN
UPDATE accstat
SET accstat.Req_Count = accstat.Req_Count - i.cnt
FROM dws..DWS_AccountStatus AS accstat
INNER JOIN (
SELECT Account_ID,
Data_Source_ID,
COUNT(*) AS cnt
FROM inserted
GROUP BY Account_ID,
Data_Source_ID
) AS i ON i.Account_ID = accstat.Account_ID
AND i.Data_Source_ID = accstat.Data_Source_ID
JOIN DELETED d
ON d.Account_ID = i.Account_ID
AND i.Data_Source_ID = d.Data_Source_ID
AND d.Query_Status <> i.Query_Status
AND i.Query_Status IN (9000,6000)
END
GO

[/code]

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

Shilpa22
Starting Member

37 Posts

Posted - 2010-08-23 : 02:43:37

Sorry...I tried the above trigger.I am getting the below error msg..
Msg 207, Level 16, State 1, Procedure AfterUpdate_JobQuery, Line 30
Invalid column name 'Query_Status'.
Msg 207, Level 16, State 1, Procedure AfterUpdate_JobQuery, Line 31
Invalid column name 'Query_Status'.
Msg 207, Level 16, State 1, Procedure AfterUpdate_JobQuery, Line 31..

Any thoughts on this?
Invalid column name 'Query_Status'.

Thanks in Advance
Shilpa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 04:47:45

i gave suggestion based on your earlier query. do you really have column called query_status in your table?

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

Shilpa22
Starting Member

37 Posts

Posted - 2010-08-23 : 05:26:15

yes I do have that column(query_status).

Thanks in Advance
Shilpa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 08:50:28

its on which table? DWS..DWS_Job_Query?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources