March 5, 2010 at 4:11 am
Hey guys,
I have an insert trigger which updates a field in another table. The trigger is shown below:
UPDATE ct
SET [text] = CASE WHEN ct.longflag = 1 THEN CAST(ct.[text] AS VARCHAR(MAX)) ELSE ct.shorttext END
+ '; ' + REPLACE(i.shortnarrative, '*Fixed fee for ', '') + '; '
FROM casetext ct
INNER JOIN inserted i ON ct.caseid = i.caseid
WHERE ct.texttype = '_B'
AND ct.longflag = 1
AND SUBSTRING(i.shortnarrative, 1, 1) = '*'
UPDATE ct
SET ct.shorttext = CASE WHEN LEN(ct.shorttext) + LEN(i.shortnarrative) < 252
THEN ct.shorttext + ' ' + + REPLACE(i.shortnarrative, '*Fixed fee for ', '') + '; '
ELSE NULL
END,
ct.longflag= CASE WHEN LEN(ct.shorttext) + LEN(i.shortnarrative) < 252
THEN ct.longflag
ELSE 1
END,
ct.[text] = CASEWHEN LEN(ct.shorttext) + LEN(i.shortnarrative) < 252
THEN ct.[text]
ELSE ct.shorttext + '; ' + REPLACE(i.shortnarrative, '*Fixed fee for ', '') + '; '
END
FROM casetext ct
INNER JOIN inserted i ON ct.caseid = i.caseid
WHERE ct.texttype = '_B'
AND ct.longflag <> 1
AND SUBSTRING(i.shortnarrative, 1, 1) = '*'
INSERT INTO casetext(caseid, texttype, textno, class, [language], modifieddate, longflag, shorttext, [text])
SELECT i.caseid, '_B', 0, NULL, NULL, NULL, 0, REPLACE(i.shortnarrative, '*Fixed fee for ', '') + '; ', NULL
FROM inserted i
WHERE NOT EXISTS
(
SELECT *
FROM casetext ct
WHERE ct.caseid = i.caseid
AND ct.texttype = '_B'
)
AND SUBSTRING(i.shortnarrative, 1, 1) = '*'
Now I seem to have this working I need to make some further changes to meet a business requirement. I need to prevent certain rows being acted upon if a specific row was entered at the same time (1 or more rows can be entered into the parent table at any one time). One of the columns in the parent table is WIPCODE, so imagine the following was entered:
WIPCODE
P001
P002
P009
P055
What I need to do is ignore the rows which have a WIPCODE of P009 and P002 if a row with a WIPCODE of P001 is inserted by the user at the same time. I've tried using something like the following but it doesn't seem to do what I want.
if (select count(*) from inserted where wipcode = 'P001') = 1
begin
delete from inserted where wipcode in ('P002', 'P009')
end
Do you have any ideas as to how I could achieve this? I hope this all makes sense and any help would be much appreciated.
Chris
March 5, 2010 at 5:55 am
Hi Chris,
It's not a good idea to "accept the insert" and not store all the rows inserted. Better to either accept the insert with all rows or reject the whole insert. I think you could achieve this by adding this code in the trigger:
if ((select count(*) from inserted where wipcode = 'P001') > 0
and (select count(*) from inserted where not wipcode = 'P001') > 0)
begin
RAISERROR ('Not allowed to insert other wipcodes together with wipcode P001!',
16, 1)
ROLLBACK TRANSACTION
end
If you for some reason want to "accept the insert" and throw away some of the inserted rows (which I wouldn't recommend), you probably can do a new insert with only the wanted rows, instead of raising an error. But this is nothing I have tested...
/Markus
March 5, 2010 at 6:53 am
Thanks for the reply. Just in case there is any confusion, I want all of the rows to be inserted into the table which has the trigger applied, but if a row which has a WIPCODE of P009 is being inserted, I don't want to update the secondary table with data from any rows which have a WIPCODE of P009 or P002.
March 5, 2010 at 7:40 am
Chris-475469 (3/5/2010)
I don't want to update the secondary table with data from any rows which have a WIPCODE of P009 or P002.
UPDATE ctSET ct.shorttext =
CASE WHEN LEN(ct.shorttext) + LEN(i.shortnarrative) < 252THEN ct.shorttext + ' ' + + REPLACE(i.shortnarrative, '*Fixed fee for ', '') + '; '
ELSE NULL
END,
ct.longflag= CASE WHEN LEN(ct.shorttext) + LEN(i.shortnarrative) < 252THEN ct.longflag
ELSE 1 END,
ct.[text] = CASEWHEN LEN(ct.shorttext) + LEN(i.shortnarrative) < 252
THEN ct.[text]
ELSE ct.shorttext + '; ' + REPLACE(i.shortnarrative, '*Fixed fee for ', '') + '; '
END
FROM casetext ct INNER JOIN inserted i
ON ct.caseid = i.caseid WHERE ct.texttype = '_B'
AND ct.longflag <> 1AND SUBSTRING(i.shortnarrative, 1, 1) = '*'
AND i.wipcode NOT ('P002', 'P009')
Try this
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 8, 2010 at 7:21 am
I'm not sure that is going to help me. I only want to ignore two specific rows if there is a row with a value of P001.
March 8, 2010 at 7:46 am
Hi Chris,
perhaps you can do something like this:
declare @StoreRows table (caseid int)
-- Make a list of all rows inserted
insert into @StoreRows
select caseid -- or what key column you have
from inserted
if ((select count(*) from inserted where wipcode = 'P001') > 0)
begin
-- if wipcode P001 is found, we remove P002 and P009 from the list
delete
from @StoreRows
where caseid in (select caseid from inserted where wipcode in ('P002', 'P009'))
end
INSERT INTO casetext(caseid, texttype, textno, class, [language], modifieddate, longflag, shorttext, [text])
SELECT i.caseid, '_B', 0, NULL, NULL, NULL, 0, REPLACE(i.shortnarrative, '*Fixed fee for ', '') + '; ', NULL
FROM inserted i
WHERE NOT EXISTS(
SELECT *
FROM casetext ct
WHERE ct.caseid = i.caseidAND ct.texttype = '_B')
AND SUBSTRING(i.shortnarrative, 1, 1) = '*'
AND i.caseid in (select caseid from @StoreRows) -- Finaly we use the list to filter out the rows stored
/Markus
March 8, 2010 at 7:52 am
I never thought of doing that but it may work. I'll play around with this and let you know how I get on.
Thanks for the suggestion!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply