SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


trigger update and compare string !?


trigger update and compare string !?

Author
Message
christophe.bernard 47659
christophe.bernard 47659
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 199
Hi,

i'm a beginner with t sql => sql server 2005.

i have this table :
T_courses with these fields : idCourse (int), Libelle varchar(300), dateCourse (datetime)

i must to create a trigger who is fired on update table => T_COURSES

but with the old libelle and new libelle i must to compare these two field AND if there is a difference fire a stored procedure

my question is: how to compare these two strings ?
here is it my code for the moment :



CREATE TRIGGER TR_COURS_UPDATE ON T_COURSES
FOR UPDATE

AS

declare @oldLibelle varchar(300)
declare @newLibelle varchar(300)

SELECT @oldLibelle = (SELECT TOP 1 FROM DELETED)
SELECT @newLibelle = (SELECT TOP 1 FROM INSERTED)

IF(oldLibelle <> @newLibelle) -- => how to compare these string
BEGIN
PRINT 'FIRE STORE PROCEDURE'
END
ELSE
BEGIN
PRINT 'NOT FIRE STORED PROCEDURE'
END



THANKS FOR YOUR TIME AND SAMPLE !

do you think my code is ok ?

happy new year..guys
Christophe
LutzM
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24059 Visits: 13559
Your approach will only compare the first updated value, whereas "first" is equal "first random" in this case since there is no ORDER BY.
When you use triggers always make sure to be ready to deal with multiple result sets.
So, instead of assigning the first value to a variable, compare the two tables (inserted and deleted) directly using NOT EXISTS().

Question aside (please, don't get me wrong): is this some kind of homework?



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
christophe.bernard 47659
christophe.bernard 47659
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 199
Hi lutz,

sorry but i can't understand !?

have you got an example or the method i must to use ?!

no it"s not a homework ;-(

thanks
christophe
LutzM
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24059 Visits: 13559
Here's a setup to play with including some tests to check whether a sproc will be fired or not.
USE tempdb
GO

if object_id('temp','U') is not null drop table temp

CREATE TABLE temp( idCourse INT, Libelle VARCHAR(300))
GO

CREATE TRIGGER compare
ON temp
INSTEAD OF UPDATE
AS
BEGIN
-- check if there is one single row where the value of column Libelle has changed
IF EXISTS
(
SELECT 1
FROM DELETED d
INNER JOIN INSERTED i ON i.idCourse = d.idCourse
WHERE i.Libelle <> d.Libelle
)
BEGIN
PRINT 'FIRE STORE PROCEDURE'
END
ELSE
BEGIN
PRINT 'NOT FIRE STORED PROCEDURE'
END
END
GO

INSERT INTO temp
SELECT 1,'1' UNION ALL
SELECT 2,'2'

UPDATE temp
SET Libelle = Libelle+1

UPDATE temp
SET Libelle = Libelle+1
WHERE idCourse = 2

UPDATE temp
SET Libelle = Libelle
WHERE idCourse = 2

UPDATE temp
SET Libelle = Libelle+1
WHERE idCourse = 3





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
christophe.bernard 47659
christophe.bernard 47659
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 199
Hi,

thanks you so much for your sample now i understand that we can add a relation between the table deleted and inserted ..

Sorry i'm a beginner and i WANT to understand Wink

thanks and have a nice year ..
Christophe
LutzM
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24059 Visits: 13559
christophe.bernard 47659 (1/6/2011)
Hi,

thanks you so much for your sample now i understand that we can add a relation between the table deleted and inserted ..

Sorry i'm a beginner and i WANT to understand Wink

thanks and have a nice year ..
Christophe


Glad I could help and welcome to SSC!!!



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
maida_rh
maida_rh
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 111
I want to compare a column value if following char are in the string then need to split it ?

MY C# CODE :


string val = strtxt.Text.Trim();
if (val.Contains("N") == true && val.Contains("E") == true && val.Contains("M") == true && val.Contains("H") == true && val.Contains("T") == true && val.Contains("V") == true && val.Contains("L") == true && val.Contains("C") == true)
{
string[] pvalues = val.Split(new char[] { 'N', 'E', 'M', 'H', 'T', 'V', 'L', 'C' });

foreach (string s in pvalues)
{
if (s.Trim() != "")
{
lstsplit.Items.Add(s);
lblmsg.Text = "split succesfully";
}
}
}
else
{
lblmsg.Text = "split cant be done";

}


I used above code to compare and split the string and want to do in sql server trigger after insert on table. Any help regarding this.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63492 Visits: 17966
maida_rh (9/26/2012)
I want to compare a column value if following char are in the string then need to split it ?

MY C# CODE :


string val = strtxt.Text.Trim();
if (val.Contains("N") == true && val.Contains("E") == true && val.Contains("M") == true && val.Contains("H") == true && val.Contains("T") == true && val.Contains("V") == true && val.Contains("L") == true && val.Contains("C") == true)
{
string[] pvalues = val.Split(new char[] { 'N', 'E', 'M', 'H', 'T', 'V', 'L', 'C' });

foreach (string s in pvalues)
{
if (s.Trim() != "")
{
lstsplit.Items.Add(s);
lblmsg.Text = "split succesfully";
}
}
}
else
{
lblmsg.Text = "split cant be done";

}


I used above code to compare and split the string and want to do in sql server trigger after insert on table. Any help regarding this.


You should not hijack someone else's thread. Instead you should start your own. I would however recommend that before you start a thread that you read the first link in my signature about best practices when posting questions. Your post raises more questions then it answers. Why in a trigger? Are you familiar with string splitting in sql? If not, then you should read the article in my signature about splitting strings. The more details you provide in your question the better the answer you will get.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
maida_rh
maida_rh
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 111
Sorry! I dont know that.I creat my thread :

http://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search