November 9, 2012 at 2:11 am
Hi All,
i'm looking to try to create a trigger that will just create a txt file in a folder when a new row is inserted in the table, however i would only like this to trigger if the row is a new value.
e.g.
say there are two columns ID, Name.
Name is not unique, so if i already have a row with "smith" in the name column, and a new row gets entered with "smith" in the name column, then it wont trigger (or at least won't create the txt file), however if the new insert is the first occurrence of "smith", then i would like it to trigger and create the txt file.
hopefully i have explained this ok, is anyone able to point me in the right direction. - i'm not sure about the uniqueness element.
Many thanks in advance
Jason
November 9, 2012 at 2:23 am
November 9, 2012 at 2:36 am
Hi Vinu,
i am not really interested in any content to be honest, the txt file will be used as a trigger for another bit of software - the only thing needed is that the txt file needs to be called the name in the column e.g. smith.txt
Many Thanks
Jason
November 9, 2012 at 5:15 am
Sorry for the late reply Jason.
If I have understood your requirement correctly then the following sample script would do what you want.........test the script with the test cases provided and further according to your requirement you can edit the script....
--Creating Table
Create Table Ex
(
Id int Identity(1,1),
Name nVarchar(30)
)
--Createing Trigger
Create Trigger Test_Trig
On Ex
After INSERT
As
Begin
Declare @val NVarchar(30), @val1 int, @sql NVarchar(400)
Select @val = Name From Inserted
Select @val1 = Count(Name) From Ex Where Name = (Select name From Inserted)
Set @sql = 'echo hello > D:\' + @val + '.txt'---------------specify path of text file according to your requirement
If (@val1 <= 1)
Begin
Execute xp_cmdshell @sql
End
End
--Testing Trigger
Insert Into Ex--------------------First Insert
Select 'Vinu'
Insert Into Ex--------------------Second Insert
Select 'Vinu'
Insert Into Ex--------------------Third Insert
Select 'Vinu'
Insert Into Ex--------------------Fourth Insert
Select 'Jason'
--Checking values in table
Select * From Ex
--Truncate Table Ex
--Check text files created at above mentioned path
NOTE : This Trigger only works for single inserts not batch inserts. Let me see if I can get something to work for Batch inserts. I'll get back to you on this.
November 9, 2012 at 8:48 am
do you only need one text file per insert? ie. If you insert bob, jim, and sally in one batch and bob and sally are unique (new) do you need one text file or 2?
Also there may be a better way to do this. What are you trying to accomplish with the text file triggering another piece of software? could you use something like (but not necessarily) xp_cmdshell in the trigger to call the program directly?
also what happens when there are millions of records in the table that have to be churned through the trigger on every insert? the trigger is probably not the best idea from a design stand point.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 11, 2012 at 9:38 pm
Yes Capn....it definitely is not a good idea to use a trigger for doing this. But, the requirement was very interesting and I just wanted to see if I could trigger something like this....since i haven't done it before.
I just wanted to show the OP that it can b done and was going to tell him that its not a good idea to do so.
Jason, I did get it to work for batch inserts......you can use a temp table to store the inserted values and then use a cursor to select each value and check with the old table if its unique or a duplicate and then create the text file.
But, this would be a very time consuming routine and would kill your performance completely. So, the bottom line is that it is a very bad idea to do what you are doing using a Trigger.
November 12, 2012 at 12:38 am
vinu512 (11/11/2012)
I did get it to work for batch inserts......you can use a temp table to store the inserted values and then use a cursor to select each value and check with the old table if its unique or a duplicate and then create the text file. But, this would be a very time consuming routine and would kill your performance completely. So, the bottom line is that it is a very bad idea to do what you are doing using a Trigger.
IF the software is using the newly created txt files then we can go with the asynchronous call where service broker OR job with above quoted approach can acheive this task and this can avoid the resource contentions
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 13, 2012 at 7:50 am
Hi All,
thanks for all the feedback, i think i am quickly learning that this may not be the best way to approach the issue i have.
i will put my thinking cap back on.
Many Thanks
Jason
November 14, 2012 at 9:22 pm
Bhuvnesh (11/12/2012)
vinu512 (11/11/2012)
I did get it to work for batch inserts......you can use a temp table to store the inserted values and then use a cursor to select each value and check with the old table if its unique or a duplicate and then create the text file. But, this would be a very time consuming routine and would kill your performance completely. So, the bottom line is that it is a very bad idea to do what you are doing using a Trigger.IF the software is using the newly created txt files then we can go with the asynchronous call where service broker OR job with above quoted approach can acheive this task and this can avoid the resource contentions
Still....the requirement seems a little over the top. I would like to know what the actual requirement is.....if its simply creating text files for every inserted row then we store the newly added names in the db itself.......it depends on what the requirement is and i would advise this should be done if and only if there is no other way to accomplish this other than using triggers to do it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply