SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ganeshn
Starting Member

India
1 Posts

Posted - 02/25/2014 :  07:59:57  Show Profile  Reply with Quote
Hi Gurus,

We are having xml data in a column. Is it possible to write a trigger to generate a mail if particular kind of data get inserted in a tag.

For ex:

<File AF="910" PTO="ATN_P76035_PSQO" NNO="54545465" KTNNN="AX2" KL="" AD="99" PrqnT="AX2" Stab="21545" KE="45454" TE="65465" Rsaa="BBBB" AK="54544.AX2.POEAX2.546546546.NONTP.NONTP" AK2="">

In the above xml data if we have the value 21545 in Stab tag the trigger needs to be executed and mail needs to be sent to a distribution list.

Please help me on this.

Thanks,
Ganesh

Edited by - ganeshn on 02/25/2014 08:02:25

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/28/2014 :  01:26:13  Show Profile  Reply with Quote
yep you can create a trigger as below

CREATE TRIGGER Trg_TableName
ON TableName
AFTER INSERT
BEGIN
IF EXISTS (SELECT 1
           FROM INSERTED t
           CROSS APPLY XmlCol.nodes('/File')t(u)
           WHERE t.u.exist('.[@Stab="21545"]')=1)
EXEC sp_send_db_mail.... your email code here
END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 02/28/2014 01:26:43
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000