Trigger is not firning for some records

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 is not firning for some records

Author

Topic

raju.venkatsv
Starting Member

2 Posts

Posted - 2014-06-18 : 11:47:57

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-18 : 11:53:39

You've writing your trigger so that it will only work on a single row (random row from the Inserted table). You need to write it so that it can handle multiple rows in the Inserted table.

raju.venkatsv
Starting Member

2 Posts

Posted - 2014-06-18 : 12:02:13

Hi Lamprey,

Thanks for your quick reply.

Could you please give some additional info on updating my trigger to meet the business requirement.

As i am not a programmer , seek your help and request you to give some useful info or hints to update trigger

Thanks
Raju

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-18 : 12:34:05

[code]CREATE TRIGGER dbo.chat_trigger
ON dbo.chat
AFTER UPDATE
AS

SET NOCOUNT ON;

INSERT dbo.chat_cn
(
{column list here}
)
SELECT i.{column list here}
FROM inserted AS i
INNER JOIN dbo.country AS c ON c.chat_id = i.chat_id
WHERE c.country_name = 'CN';

INSERT dbo.chat_in
(
{column list here}
)
SELECT i.{column list here}
FROM inserted AS i
INNER JOIN dbo.country AS c ON c.chat_id = i.chat_id
WHERE c.country_name = 'IN';

INSERT dbo.chat_tw
(
{column list here}
)
SELECT i.{column list here}
FROM inserted AS i
INNER JOIN dbo.country AS c ON c.chat_id = i.chat_id
WHERE c.country_name = 'TW';[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources