Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

trigger update and compare string !? Expand / Collapse
Author
Message
Posted Wednesday, January 5, 2011 4:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 10, 2014 4:27 AM
Points: 153, Visits: 133
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
Post #1042941
Posted Wednesday, January 5, 2011 4:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 6,840, Visits: 13,354
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
Post #1042945
Posted Wednesday, January 5, 2011 6:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 10, 2014 4:27 AM
Points: 153, Visits: 133
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

Post #1042992
Posted Wednesday, January 5, 2011 7:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 6,840, Visits: 13,354
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
Post #1043081
Posted Thursday, January 6, 2011 7:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 10, 2014 4:27 AM
Points: 153, Visits: 133
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 ;)

thanks and have a nice year ..
Christophe
Post #1043745
Posted Thursday, January 6, 2011 7:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 6,840, Visits: 13,354
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 ;)

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
Post #1043773
Posted Wednesday, September 26, 2012 6:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
Points: 46, 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.
Post #1364604
Posted Wednesday, September 26, 2012 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:15 PM
Points: 13,077, Visits: 12,523
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 Moden's 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)
Post #1364693
Posted Thursday, September 27, 2012 11:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:39 AM
Points: 46, Visits: 111
Sorry! I dont know that.I creat my thread :

http://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspx
Post #1365607
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse