June 18, 2014 at 9:55 am
Hi ,
1) I have two tables , chat and country tables , chat table has 3 columns(chat_id,language,chat_info) and media table has 2 fields(chat_id ,country), chat table will store all countries data (India,china,taiwan)
2) chat_id,language will be populated by insert statement, and chat_info by update statement, i have a below after update trigger on chat table,which should verify country from media table and copy respective record from chat table to chat_country table (chat_in,chat_cn,chat_tw - these tables will have same structure as chat table)
3) this trigger is not firing for some of the records ,no clue or info for which it is missing some records, need your expertise suggestions to resolve this issue
-------------------------
create TRIGGER [dbo].[chat_trigger] ON [test].[dbo].[chat]
after update
as
BEGIN
set nocount on
DECLARE @CHATID nchar(32)
DECLARE @COUNTRY VARCHAR(2)
SELECT @chat_id=(CHAT_ID)FROM inserted
Select @country=(country_name) from test.dbo.country where chat_id=@chat_id
IF @COUNTRY = 'CN' BEGIN
insert into chat_cn select * from chat where chat_id = @CHATID
END
ELSE IF @COUNTRY = 'IN' BEGIN
insert chat_in select * from chat where chat_id = @CHATID
END
ELSE IF @COUNTRY = 'TW' BEGIN
insert into chat_tw select * from chat where chat_id = @CHATID
END
End
--------------------------------------
Thanks in advance.
Thanks
Raju
June 18, 2014 at 10:06 am
The trigger assumes there's only every 1 row in inserted. If you update multiple rows that will not be the case.
You need to change the trigger to handle any number of rows in inserted/deleted. Hint, that means no SELECT @var = Column from inserted
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2014 at 10:16 am
Thank you for your quick reply.
request you to provide some hints or some information , using which i can update my trigger to meet business requirement.z
requesting you for some inputs as i am not expertise in programming.
Thanks
Raju
June 18, 2014 at 10:28 am
raju.venkatsv (6/18/2014)
Thank you for your quick reply.request you to provide some hints or some information , using which i can update my trigger to meet business requirement.z
requesting you for some inputs as i am not expertise in programming.
Thanks
Raju
It only needs a set based approach, something along these lines
😎
create TRIGGER [dbo].[chat_trigger] ON [test].[dbo].[chat]
after update
as
BEGIN
set nocount on
INSERT INTO chat_cn
SELECT C.* from chat C
INNER JOIN inserted I
ON C.chat_id = I.chat_id
INNER JOIN test.dbo.country CN
ON I.chat_id = CN.chat_id
WHERE CN.country_name = 'CN'
INSERT INTO chat_in
SELECT C.* from chat C
INNER JOIN inserted I
ON C.chat_id = I.chat_id
INNER JOIN test.dbo.country CN
ON I.chat_id = CN.chat_id
WHERE CN.country_name = 'IN'
INSERT INTO chat_tw
SELECT C.* from chat C
INNER JOIN inserted I
ON C.chat_id = I.chat_id
INNER JOIN test.dbo.country CN
ON I.chat_id = CN.chat_id
WHERE CN.country_name = 'TW'
June 18, 2014 at 10:33 am
I think you are looking for something like this:
CREATE TRIGGER [dbo].[chat_trigger] ON [test].[dbo].[chat]
AFTER UPDATE
AS
BEGIN;
SET NOCOUNT ON;
IF EXISTS ( SELECT
1
FROM
inserted AS I
JOIN dbo.country AS C
ON I.chatid = C.chatid
WHERE
C.country_name = 'CN' )
BEGIN;
INSERT INTO chat_cn
(
chat_id,
language,
chat_info
)
SELECT
I.chat_id,
I.language,
I.chat_info
FROM
inserted AS I
JOIN dbo.country AS C
ON I.chatid = C.chatid
WHERE
C.country_name = 'CN';
END;
IF EXISTS ( SELECT
1
FROM
inserted AS I
JOIN dbo.country AS C
ON I.chatid = C.chatid
WHERE
C.country_name = 'IN' )
BEGIN;
INSERT INTO chat_in
(
chat_id,
language,
chat_info
)
SELECT
I.chat_id,
I.language,
I.chat_info
FROM
inserted AS I
JOIN dbo.country AS C
ON I.chatid = C.chatid
WHERE
C.country_name = 'IN';
END;
IF EXISTS ( SELECT
1
FROM
inserted AS I
JOIN dbo.country AS C
ON I.chatid = C.chatid
WHERE
C.country_name = 'TW' )
BEGIN;
INSERT INTO chat_tw
(
chat_id,
language,
chat_info
)
SELECT
I.chat_id,
I.language,
I.chat_info
FROM
inserted AS I
JOIN dbo.country AS C
ON I.chatid = C.chatid
WHERE
C.country_name = 'TW';
END;
END;
Notice I explicitly included column lists in the INSERT and SELECT statements which will protect you from getting errors if someone makes a change to one of the tables.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 30, 2014 at 9:10 am
Hi Jack,
I am exactly looking for this solution, Thank you very much.
But when I run the update query concurrently from different threads, I am getting exception as "Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim". Based on my understanding same table is been used by multiple update statements.
Do we have any technique to control it as row level locking instead table level locking?
Thanks in advance.
July 30, 2014 at 9:15 am
How do you know that there's table-level locking being used?
Deadlock graph please, table definitions, index definitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply