September 10, 2008 at 1:12 pm
I have the trigger coded below. Now, if there is no record returned, it must still be
trying to insert an empty record into ptEncounterDetailsCharges because I'm getting an
error:
"Cannot insert the value NULL into column 'DOS', table 'dbo.ptEncounterDetailsCharges';
column does not allow nulls. INSERT fails."
Now, how do I omit the insertion if the select returns nothing? I know I can use an IF
EXISTS but wouldn't that be like querying the same data twice?
Also, we need to update the record in ptEncounterDetailsCharges when the corresponding record in ptChemoOrderDetails changes, I'm not sure how to do that or if we want to do it in the same trigger or if we want to write a separate Update trigger.
CREATE TRIGGER [tr_ptChemoOrderDetails_StoreCharges]
ON [dbo].[ptChemoOrderDetails]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
-- INFUSION DRUG
INSERT INTO ptEncounterDetailsCharges
SELECT
o.DateOfService AS [DOS]
, o.MRN AS [MRN]
, 0 AS [ChargeTypeCode]
, d.LineID AS [ChargeSourceID]
, m.CPT AS [ChargeCode]
, 'INFUSIONDRUG' AS [ChargeSource]
, '1. ' + ISNULL(d.StartTime, '?') + ' - ' + ISNULL(d.StopTime, '?') + ' '
+ ISNULL(d.Push, '') + char(13) + char(10) +
'2. ' + CAST(ISNULL(l.Unit, 0.00) AS varchar) + ' '
+ ISNULL(l.DrugDescription, '') + char(13) + char(10) +
'3. ' + ISNULL(d.AdminDetails, '') + char(13) + char(10) +
'4. ' + ISNULL(d.Dosage, '') + ' ' + ISNULL(d.Unit, '') AS [COMMENT]
FROM inserted d -- ptChemoOrderDetails
INNER JOIN ptChemoOrders o ON o.OrderID = d.OrderID
INNER JOIN coRegimenMeds m ON d.MedID = m.ID
FULL OUTER JOIN LYNXBilling l ON m.CPT = l.CPT
AND l.DateOfService = o.DateOfService
AND l.PID = o.MRN
WHERE (d.Given = 0 OR d.Given IS NULL)
AND ((m.DilutionMedID IS NULL) OR (m.DilutionMedID = 0))
-- FLUIDS
INSERT INTO ptEncounterDetailsCharges
SELECT
o.DateOfService AS [DOS]
, o.MRN AS [MRN]
, 0 AS [ChargeTypeCode]
, d.LineID AS [ChargeSourceID]
, m.CPT AS [ChargeCode]
, 'INFUSIONDRUG' AS [ChargeSource]
, '1. FLUIDS' + CHAR(13) + CHAR(10) +
'2. Bag ' + ISNULL(d.Mixed, '') + CHAR(13) + CHAR(10) +
'3.' + CHAR(13) + CHAR(10) +
'4. ' + CONVERT(VARCHAR,ISNULL(d.FluidAmt, 0.00)) + ' ' + CONVERT(VARCHAR,ISNULL(d.FluidUnit, 0)) AS [COMMENT]
FROM inserted d -- ptChemoOrderDetails
INNER JOIN ptChemoOrders o ON o.OrderID = d.OrderID
INNER JOIN coRegimenMeds m ON d.MedID = m.ID
WHERE (d.Given = 0 OR d.Given IS NULL )
AND (m.DilutionMedID = 1)
AND (d.Fluids IS NOT NULL)
AND (d.FluidAmt > '0')
SET NOCOUNT OFF
END
September 10, 2008 at 2:42 pm
I think you select IS returning something and the DOS field is NULL in the record(s) that are returned. If no records are returned, nothing will be inserted (which is what you want). Here's a quick example:
create table blah (pk int)
insert into blah
select 1 from sysobjects where 1=0
select * from blah
--no rows returned
You can update changed records in the same trigger if you expand it to be FOR INSERT,UPDATE or you can create another trigger that is just FOR UPDATE. If you're all about efficency, its probably better to have two seperate triggers so that you don't have to determine what to do (an insert vs. an update). If you want to keep all your code in one place, just create one trigger.
Hope this helps!
Chad
September 10, 2008 at 2:56 pm
Even if nothing is inserted, the INSERT trigger still fires. You may want to add a test at the beginning of the trigger to test the number of rows in the INSERTED table, if > 0 do the work.
😎
September 10, 2008 at 4:14 pm
I had to test for a certain value to do each of the inserts and now have it working with
no errors.
However, I am at a loss as to how to do the update trigger with this code. I will be working
on both an update and delete trigger tomorrow.
September 11, 2008 at 7:36 am
How would I use ColumnsUpdated() to access the columns that were updated in
the ptChemoOrderDetails table? I intend to write a separate UPDATE trigger.
These are the 9 columns that could affect the data that is used in the insert trigger
that need to be checked during an update.
Name ColumnID
LineID 2
MedID 3
Dosage 7
Unit 8
StartTime 10
StopTime 11
AdminDetails 12
Push 13
Given 23
BTW, there will be using a mixture of SQL2000 and SQL2K5 so the value to use in
the bitmask needs to be aware of that.
Once I get done with this one, I should be able to figure out the other 5 I have to do.
September 11, 2008 at 11:37 am
Nevermind, I figured it out myself and have 6 sets of working insert and update triggers.
September 12, 2008 at 2:03 pm
Glad you got it working. Be careful with the ColumnsUpdated - my experience has been that it returns true if the column was part of the update, not if the value itself was updated. So:
UPDATE mytable
SET col1=col1
will cause ColumnsUpdated to return true for col1. If you want to know if the value changed, you have to join the inserted and deleted tables and check to see if col1 != col2.
Good luck!
Chad
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply