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

Trigger

Author

Topic

ganeshn
Starting Member

1 Post

Posted - 2014-02-25 : 07:59:57

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-28 : 01:26:13

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources