Create trigger - 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

General SQL Server Forums

New to SQL Server Programming

Create trigger

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-07-18 : 13:33:33

I have a table called Promotion with fields Id, Keyword(varchar), SmsBody(varchar).

Have another table called New_Groups with fields Id, GID(numeric), Keyword(varchar).

and have one more table called New_Contacts with fields Id, MobileNumber(varchar), Name(varchar), GroupID(varchar)

Here I need to write one trigger to full fill my below request please,
When a new record comes to the table Promotion then this trigger will check the corresponding Keyword in the groups table and will get GID value.[GID value will be consecutive. means for first group GID = 1, for second group GID = 2,for third group GID = 3, etc]. After getting this GID then the trigger will count number of groups from the Groups table then it will make a string like '000'. then trigger will insert a new record in the New_Contacts table

Let me say an example:

Promotion table
Id <><><>Keyword<><><>SmsBody
1 <><><><>LG12345<><><><>Hello how are you?
2 <><><><>TT55555<><><><>welcome

New_Groups table
Id <><><>GID<><><>Keyword
1 <><><><>1<><><><>LG12345
5 <><><><>2<><><><>TT55555
8 <><><><>3<><><><>XYZ0000

New_Contacts table
Suppose if I received first record in Promotion table with Keyword 'TT55555', (see the Promotion table first record) then the trigger will get GID value to this keyword from Groups table. Here GID value for keyword 'TT55555' is : 2 (see the New_Groups table records). then again trigger will count the number of groups. Here its is : 3. So the GroupId for New_Contacts table will be '010'. (010 means this mobile number registered in the second group, because the second character is 1 in '010' string)

Id <><><>MobileNumber<><><>Name<><><>GroupId
1 <><><><>9198XXXXXX<><><><> none<><><><> 010

And another record I received in Promotion table wtih Keyword 'XYZ0000' (see the Promotion table second record), then the trigger will get GID value to this keyword from Groups table. Here GID value for keyword 'TT55555' is : 3 (see the New_Groups records). then again trigger will count the number of groups. Here its is : 3. So the GroupId for New_Contacts table will be '001'. (001 means this mobile number registered in the third group, because the third character is 1 in '001' string)
2 <><><><>9198XXXXXX<><><><>none<><><><> 001
and so on.

so how to create this trigger. I am using sql2000 server.
All helps are highly appreciated!

Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 13:53:12

something like this

CREATE TRIGGER YourTrigger
ON Promotion
AFTER INSERT
AS
BEGIN
INSERT INTO New_Contacts (MobileNumber,Name,GroupId)
SELECT mobnumbervalue,'None',
case ng.GID when 1 then 100
         when 2 then 010
         when 3 then 001
end
FROM INSERTED i
INNER JOIN New_Groups ng
ON ng.GID=i.Id
END

i dont know from where you get the mobileno though.you need to fill it yourself

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-07-18 : 14:32:09

Dear visakh16,
Thanks,
Here the Mobile Number I am getting from the Promotion table. So the Prmotion table structure would be:
Promotion with fields Id, Keyword(varchar), SmsBody(varchar), MobileNumber(varchar).
and also here I need to count number of groups in the New_Groups table also. Could you check it again please

Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 14:43:29

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-07-19 : 03:48:21

Dear Mr.visakh16,
I made some changes on this trigger,

CREATE TRIGGER YourTrigger
ON Promotion
AFTER INSERT
AS
BEGIN
INSERT INTO New_Contacts (MobileNumber,Name,GroupId)
SELECT i.MobileNumber,'None',
STUFF(REPLICATE('0',t.TotalCount),ng.GID,1,'1')
FROM INSERTED i
INNER JOIN New_Groups ng
'ON ng.GID=i.Id

here I need to get the corresponding GID from New_Groups table where keyword from Promotion table is equal to the Keyword in New_Groups. So that I made some chages in the beow line but it shows error

ON ng.GId = (select GId from New_Groups Where Keyword = i.Keyword)

So if any record comes to the Promotion table first the triger should check the keyword in New_Groups table then it will get the corresponding GID value and then by using this GID value the triger will make a string like '010' and will insert inthe New_Contacts table

CROSS JOIN (SELECT COUNT(*) AS TotalCount
FROM New_Groups)t
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-19 : 12:08:27

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-07-19 : 14:24:25

Dear Mr.Visakh16,
When I execute this trigger it shows tow problems
Server: Msg 8116, Level 16, State 1, Procedure YourTrigger, Line 6
Argument data type numeric is invalid for argument 2 of stuff function.
Server: Msg 446, Level 16, State 1, Procedure YourTrigger, Line 6
Cannot resolve collation conflict for equal to operation.

Help me to solve this issue.

Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-19 : 14:33:41

quote:
Originally posted by shajimanjeri

Dear Mr.Visakh16,
When I execute this trigger it shows tow problems
Server: Msg 8116, Level 16, State 1, Procedure YourTrigger, Line 6
Argument data type numeric is invalid for argument 2 of stuff function.
Server: Msg 446, Level 16, State 1, Procedure YourTrigger, Line 6
Cannot resolve collation conflict for equal to operation.

Help me to solve this issue.

Shaji

CREATE TRIGGER YourTrigger
ON Promotion
AFTER INSERT
AS
BEGIN
INSERT INTO New_Contacts (MobileNumber,Name,GroupId)
SELECT i.MobileNumber,'None',
STUFF(REPLICATE('0',t.TotalCount),CAST(ng.GID as int),1,'1')
FROM INSERTED i
INNER JOIN New_Groups ng
ON ng.Keyword=i.Keyword COLLATE (collation of New_Groups.Keyword)
CROSS JOIN (SELECT COUNT(*) AS TotalCount
            FROM New_Groups)t
END

find out the collations of Keyword fields in both tables (New_Groups & Promotion). it can be obtained by taking
sp_help tablename which list all columns along with their collations. then use COLLATE as above

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-07-19 : 14:58:34

Dear mr.visakh16,

Its working smoothly after resolving collation problem... Again my thanks mr.visakh16.
Now my last query is, in the promotion table I have one more field called MsgType (char). Here I need to check whether MsgType = 2 or no?. If MsgType = 2 then only need to proceed this trigger. I mean in this table I am storing records other than MsgType = 2 also.
And also need to check whether this mobile number already exist in the corresponding group or not?
I hope you will give a solution for this also please.

Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-20 : 13:09:52

[code]CREATE TRIGGER YourTrigger
ON Promotion
AFTER INSERT
AS
BEGIN
INSERT INTO New_Contacts (MobileNumber,Name,GroupId)
SELECT i.MobileNumber,'None',
STUFF(REPLICATE('0',t.TotalCount),CAST(ng.GID as int),1,'1')
FROM INSERTED i
INNER JOIN New_Groups ng
ON ng.Keyword=i.Keyword COLLATE (collation of New_Groups.Keyword)
LEFT OUTER JOIN New_Contacts nc
ON nc.MobileNumber=i.MobileNumber
AND nc.GroupId=STUFF(REPLICATE('0',t.TotalCount),CAST(ng.GID as int),1,'1')
CROSS JOIN (SELECT COUNT(*) AS TotalCount
FROM New_Groups)t
WHERE i.MsgType = 2
AND nc.Id IS NULL
END[/code]

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2008-07-20 : 14:48:06

Dear Mr. Visakh16,
After I execute this query I am getting the following error:
Server: Msg 107, Level 16, State 2, Procedure shaji, Line 6
The column prefix 't' does not match with a table name or alias name used in the query.

Shaji

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 06:02:22

just change the derived table order

CREATE TRIGGER YourTrigger
ON Promotion
AFTER INSERT
AS
BEGIN
INSERT INTO New_Contacts (MobileNumber,Name,GroupId)
SELECT i.MobileNumber,'None',
STUFF(REPLICATE('0',t.TotalCount),CAST(ng.GID as int),1,'1')
FROM INSERTED i
INNER JOIN New_Groups ng
ON ng.Keyword=i.Keyword COLLATE (collation of New_Groups.Keyword)
CROSS JOIN (SELECT COUNT(*) AS TotalCount
            FROM New_Groups)t
LEFT OUTER JOIN New_Contacts nc
ON nc.MobileNumber=i.MobileNumber
AND nc.GroupId=STUFF(REPLICATE('0',t.TotalCount),CAST(ng.GID as int),1,'1')
WHERE i.MsgType = 2
AND nc.Id IS NULL
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources