May 14, 2003 at 6:29 am
Hi, I would like to append characters to a field value in a table, based on the value of a field in a separate, related table.
I am not sure on the best method for this:
I have 2 tables:
1) tblStore
2) tblStoreProfile
SCHEMA
1) tblStore
storeId INT
storeName varchar(30)
2) tblStoreProfile
storeId INT refs tblStore.storeId
isBigStore BIT
EXAMPLE:
IF tblStore.storeName is “Aberdeen”
AND tblStoreProfile.isBigStore = 0
THEN tblStore.storeName remains unchanged, ie “Aberdeen”
However, if tblStoreProfile.isBigStore = 1
Then I would like to append the characters: “(b)” to the
tblStore.storeName
Therefore, the tblStore .storeName “Aberdeen” would now become
“Aberdeen (b)”
Would triggers be the best way to accomplish this?please forgive the dodgy explanation.I can clarify any of my explanation.
Thanks for reading.
Cheers, yogiberr
Edited by - yogiberr on 05/14/2003 06:31:39 AM
Edited by - yogiberr on 05/14/2003 06:33:46 AM
Edited by - yogiberr on 05/14/2003 06:36:45 AM
Edited by - yogiberr on 05/14/2003 06:37:39 AM
May 14, 2003 at 6:48 am
You could run this script periodically:
UPDATE s
SET s.storeName = s.storeName + ' (b)'
FROM tblStore s
INNER JOIN tblStoreProfile p
ON s.storeID = p.storeID
WHERE p.isBigStore = 1
Or you could handle the logic in the stored procedure(s) which add/edit the tblStore record.
Or you could indeed put the logic in a trigger running on tblStore which checks the profile and updates the name if needed.
May 14, 2003 at 12:27 pm
Hi buddy, I went for the “trigger” option.Cheers for the advice.
I created a trigger on tblStoreProfile, that will update s.storeName if the
Value of p.isBigStore is changed.
I am new to triggers, but I got as far as:
CREATE TRIGGER [trigBigStore] ON [dbo].[tblStoreProfile]
FOR UPDATE
AS
DECLARE @OLDisBigStore BIT
SELECT @ OLDisBigStore = (SELECT isBigStore FROM Deleted)
DECLARE @ NEWisBigStore BIT
SELECT @ NEWisBigStore = (SELECT isBigStore FROM Inserted)
IF UPDATE(isBigStore)
BEGIN
UPDATE s
SET s.storeName = s.storeName + ' (b)'
FROM tblStore s, tblStoreProfile p
WHERE s.clientId = @clientId
AND s.storeId = @storeId
AND p.clientId = s.clientId
AND p.storeId =s.storeID
AND p.isBigStore = @ NEWisBigStore
END
This seems to work ok in the most part.
However, the “IF UPDATE(isBigStore)”
Statement runs, even if the updated value is the same as the original one.
Here is the update statement that I used:
UPDATE tblStoreProfile
SET isTravelator = 0
where clientId = 1
and storeId = 5
So, I have had to put the following statement in to the trigger:
IF UPDATE(isTravelator)
AND @OLDisBigStore= 0
AND @ NEWisBigStore <> @ OLDisBigStore
BEGIN
…
I read in BOL that “IF UPDATE” only executes if the updated value differs from the original one, yet it executes, regardless of whether the updated field is the same as the original one.
I’m not sure what I am doing wrong.
BTW, in plain english, what I want to do is
1) APPEND '(b)' to the tblStore.storeName
when tblStoreProfile.isBigStore is changed to 1
2) REMOVE '(b)' from tblStore.storeName when tblStoreProfile.isBigStore is changed to 0
Can anyone spot the problem?
Many thanks for reading.
yogi
May 15, 2003 at 12:39 am
Hi,
I use triggers to monitor data changes in some tables.
What actually happens in an update is a delete and an insert operation. SQL Server maintains therefore two special tables inserted and deleted. Search in BOL for 'triggers, inserted tables', this will lead you to a topic named 'Using the inserted and deleted Tables'.
The following is a snippet from one of my triggers. I think it can easily be transformed to fit your needs.
[...]
SELECT 'OLD',
del.KapitalanlagenID,
FROM deleted del
[... some insert action in my case]
SELECT 'NEW',
ins.KapitalanlagenID,
FROM inserted ins
END
Are these tables frequently used?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 15, 2003 at 2:25 pm
Hi Frank,
thanks for the reply.
yes, I use these tables often.What I don't understand is, why if I run the SAME update statement multiple times on the SAME table,
eg.
UPDATE tblStoreProfile
SET isBigStore = 0
WHERE storeId = 5
That the
"IF UPDATE(isBigStore)
BEGIN
...
"
sql of my trigger executes every time, even though I am running the same update, and
and am NOT actually inserting a different value into the table.I'm pretty confused, I thought that the IF UPDATE clause was only equal to TRUE when the data in the table actually changed?
Sorry for the hassle, am I missing something?
cheers,
yogi.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply