January 5, 2011 at 4:28 am
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
January 5, 2011 at 4:42 am
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?
January 5, 2011 at 6:18 am
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
January 5, 2011 at 7:48 am
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
January 6, 2011 at 7:28 am
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
January 6, 2011 at 7:52 am
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!!!
September 26, 2012 at 6:12 am
[font="Arial Black"]I want to compare a column value if following char are in the string then need to split it ?
MY C# CODE :[/font]
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";
}
[font="Arial Black"]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.[/font]
September 26, 2012 at 7:52 am
maida_rh (9/26/2012)
[font="Arial Black"]I want to compare a column value if following char are in the string then need to split it ?MY C# CODE :[/font]
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";
}
[font="Arial Black"]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.[/font]
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 27, 2012 at 11:07 pm
Sorry! I dont know that.I creat my thread :
http://www.sqlservercentral.com/Forums/Topic1365604-392-1.aspx
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply