February 23, 2012 at 6:39 pm
I have the query to prevent deletion of a row when doing so will cause it to fall beneathe a specified number. But i also believe that I should be able to delete this row if the parent table's row is deleted first. I have some faulty code that achieves this but it gives me an error so it doesn't work perfectly.
The Cardinality should only be enforced when I'm trying to delete a line item only. When I try to delete a whole billing statement, it should allow the line items to cascade delete.
The first part of the IF statement works when there is only one record in the BillingStatements table (but with errors). But if there is more than one Billingstatements record per patient, it doesn't work.
CREATE TRIGGER trigEnforceMinCardinality ON LineItems
FOR DELETE
AS
BEGIN
/*
Echo the contents of the "deleted" table
The "deleted" table contains the values of the deleted row.
*/
SELECT 'deleted' AS Buffer, deleted.*, 'BEFORE' AS RowImage FROM deleted
--Action...
IF (SELECT COUNT(*)
FROM BillingStatements
WHERE BillingStatementID = (SELECT DISTINCT BillingStatementID FROM deleted)) = 0
BEGIN
PRINT 'No Billing Statement was found delete all line items'
COMMIT TRANSACTION
END
ELSE
IF (SELECT COUNT(*)
FROM LineItems
WHERE BillingStatementID = (SELECT DISTINCT BillingStatementID FROM deleted)) < 1
BEGIN
ROLLBACK TRAN
RAISERROR ('A billing statement must have at least one line item.', 16, 1)
END
END
GO
Here is the whole setup
/* Create and Use Database */
USE Master
GO
CREATE DATABASE OakridgeCentre
GO
USE OakridgeCentre
GO
/*
USE Master
DROP DATABASE OakridgeCentre
*/
SET NOCOUNT ON
/* Create Tables */
-- Unfortunately, this design could cause users to add filler for Address and Phone as these become unknown or invalid.
-- In a real world situation, this problem can be resolved by separating Address and Phone out. This will bring the tables to 3NF.
CREATE TABLE Patients
(
PatientID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
PatientNumber CHAR(6) NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
[Address] VARCHAR(100) NOT NULL,
Phone CHAR(12) NOT NULL,
LastChangedBy INT
)
GO
-- Log Table
CREATE TABLE logPatients
(
AuditLogIDINT IDENTITY(1,1) PRIMARY KEY,
PatientID INT,
PatientNumber CHAR(6),
FirstName VARCHAR(30),
LastName VARCHAR(30),
[Address] VARCHAR(100),
Phone CHAR(12),
ChangedByINT, --PK of person making change
ChangeTimeDATETIME DEFAULT GETDATE(), --Time change occurred
ChangeTypeNVARCHAR(10), --INSERT, UPDATE, DELETE
ImageTypeNVARCHAR(10) --BEFORE, AFTER
)
GO
-- Provider is really a type of person along with Patient. They could be combined into one entity or be divided into super/sub types.
-- Nothing logically prevents a provider from also being a patient except this scenario.
CREATE TABLE Providers
(
ProviderID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ProviderNumber CHAR(6) NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
Title VARCHAR(30) NULL,
[Address] VARCHAR(100) NOT NULL,
Phone CHAR(12) NOT NULL,
LastChangedBy INT
)
GO
-- Log Table
CREATE TABLE logProviders
(
AuditLogIDINT IDENTITY(1,1) PRIMARY KEY,
ProviderID INT,
ProviderNumber CHAR(6),
FirstName VARCHAR(30),
LastName VARCHAR(30),
Title VARCHAR(30),
[Address] VARCHAR(100),
Phone CHAR(12),
ChangedByINT, --PK of person making change
ChangeTimeDATETIME DEFAULT GETDATE(), --Time change occurred
ChangeTypeNVARCHAR(10), --INSERT, UPDATE, DELETE
ImageTypeNVARCHAR(10) --BEFORE, AFTER
)
GO
-- Provider is really a type of person along with Patient. They could be combined into one entity or be divided into super/sub types.
CREATE TABLE PaymentTypes
(
PaymentTypeID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Description] VARCHAR(30) NOT NULL,
LastChangedBy INT
)
GO
-- Log Table
CREATE TABLE logPaymentTypes
(
AuditLogIDINT IDENTITY(1,1) PRIMARY KEY,
PaymentTypeID INT,
[Description] VARCHAR(30),
ChangedByINT, --PK of person making change
ChangeTimeDATETIME DEFAULT GETDATE(), --Time change occurred
ChangeTypeNVARCHAR(10), --INSERT, UPDATE, DELETE
ImageTypeNVARCHAR(10) --BEFORE, AFTER
)
GO
CREATE TABLE BillingStatements
(
BillingStatementID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
AmountDue MONEY NOT NULL,
TotalItems INT NOT NULL,
TotalCharges MONEY NOT NULL,
TotalCredits MONEY NOT NULL,
AmountEnclosed MONEY NOT NULL,
[Date] DATETIME NOT NULL,
[Signature] BIT,
LastChangedBy INT
)
GO
-- Log Table
CREATE TABLE logBillingStatements
(
AuditLogIDINT IDENTITY(1,1) PRIMARY KEY,
BillingStatementID INT,
AmountDue MONEY,
TotalItems INT,
TotalCharges MONEY,
TotalCredits MONEY,
AmountEnclosed MONEY,
[Date] DATETIME,
[Signature] BIT,
ChangedByINT, --PK of person making change
ChangeTimeDATETIME DEFAULT GETDATE(), --Time change occurred
ChangeTypeNVARCHAR(10), --INSERT, UPDATE, DELETE
ImageTypeNVARCHAR(10) --BEFORE, AFTER
)
GO
CREATE TABLE LineItems
(
LineItemID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Date] DATETIME NULL DEFAULT GETDATE(),
Quantity INT NULL DEFAULT 1,
LastChangedBy INT
)
GO
-- Log Table
CREATE TABLE logLineItems
(
AuditLogIDINT IDENTITY(1,1) PRIMARY KEY,
LineItemID INT,
[Date] MONEY,
Quantity INT,
ChangedByINT, --PK of person making change
ChangeTimeDATETIME DEFAULT GETDATE(), --Time change occurred
ChangeTypeNVARCHAR(10), --INSERT, UPDATE, DELETE
ImageTypeNVARCHAR(10) --BEFORE, AFTER
)
GO
CREATE TABLE MedicalProcedures
(
MedicalProcedureID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
ProcedureCode CHAR(5) NOT NULL,
[Description] VARCHAR(100) NOT NULL,
Price MONEY NOT NULL,
LastChangedBy INT
)
GO
-- Log Table
CREATE TABLE logMedicalProcedures
(
AuditLogID INT IDENTITY(1,1) PRIMARY KEY,
MedicalProcedureID INT,
ProcedureCode CHAR(5),
[Description] VARCHAR(100),
Price MONEY,
ChangedBy INT, --PK of person making change
ChangeTime DATETIME DEFAULT GETDATE(), --Time change occurred
ChangeType NVARCHAR(10), --INSERT, UPDATE, DELETE
ImageType NVARCHAR(10) --BEFORE, AFTER
)
GO
CREATE TABLE Payments
(
PaymentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Amount MONEY NOT NULL,
[Date] DATETIME NOT NULL,
LastChangedBy INT
)
GO
-- Log Table
CREATE TABLE logPayments
(
AuditLogID INT IDENTITY(1,1) PRIMARY KEY,
PaymentID INT,
Amount MONEY,
[Date] DATETIME,
ChangedBy INT, --PK of person making change
ChangeTime DATETIME DEFAULT GETDATE(), --Time change occurred
ChangeType NVARCHAR(10), --INSERT, UPDATE, DELETE
ImageType NVARCHAR(10) --BEFORE, AFTER
)
GO
-- Informs us that tables have been constructed
PRINT 'Tables successfully constructed...'
/* Create FKs and Enforce Constraints */
ALTER TABLE Patients
ADD GuarantorID INT NULL,
CONSTRAINT Patients_SelfJoin_GuarantorID_fk FOREIGN KEY (GuarantorID) REFERENCES Patients(PatientID),
CONSTRAINT Patients_PatientNumber_UNIQUE UNIQUE(PatientNumber)
GO
ALTER TABLE logPatients
ADD GuarantorID INT
GO
ALTER TABLE Providers
ADD CONSTRAINT Providers_ProviderNumber_UNIQUE UNIQUE(ProviderNumber)
GO
ALTER TABLE BillingStatements
ADD PatientID INT NOT NULL,
ProviderID INT NOT NULL,
CONSTRAINT BillingStatements_PatientID_fk FOREIGN KEY (PatientID) REFERENCES Patients(PatientID),
CONSTRAINT BillingStatements_ProviderID_fk FOREIGN KEY (ProviderID) REFERENCES Providers(ProviderID)
GO
ALTER TABLE logBillingStatements
ADD PatientID INT NOT NULL,
ProviderID INT NOT NULL
GO
ALTER TABLE LineItems
ADD BillingStatementID INT NOT NULL,
PaymentID INT NULL,
MedicalProcedureID INT NULL,
CONSTRAINT LineItems_BillingStatementID_fk FOREIGN KEY (BillingStatementID) REFERENCES BillingStatements(BillingStatementID) ON DELETE CASCADE,
CONSTRAINT LineItems_MedicalProcedureID_fk FOREIGN KEY (MedicalProcedureID) REFERENCES MedicalProcedures(MedicalProcedureID),
CONSTRAINT LineItems_PaymentID_fk FOREIGN KEY (PaymentID) REFERENCES Payments(PaymentID)
GO
ALTER TABLE logLineItems
ADD BillingStatementID INT,
PaymentID INT,
MedicalProcedureID INT
GO
ALTER TABLE MedicalProcedures
ADD CONSTRAINT MedicalProcedures_ProcedureCode_UNIQUE UNIQUE(ProcedureCode)
GO
ALTER TABLE Payments
ADD PaymentTypeID INT NOT NULL,
PatientID INT NOT NULL,
CONSTRAINT Payments_PaymentTypeID_fk FOREIGN KEY (PaymentTypeID) REFERENCES PaymentTypes(PaymentTypeID),
CONSTRAINT Payments_PatientID_fk FOREIGN KEY (PatientID) REFERENCES Patients(PatientID)
GO
ALTER TABLE logPayments
ADD PaymentTypeID INT NOT NULL,
PatientID INT NOT NULL
GO
ALTER TABLE MedicalProcedures
ADD CONSTRAINT ckValidPrice
CHECK (MedicalProcedures.Price BETWEEN 0 AND 1000000)
GO
PRINT 'Constraints successfully applied...'
GO
/* Create Audit Triggers */
CREATE TRIGGER trAuditPatients_Inserts ON Patients
FOR INSERT
AS
BEGIN
/*
Echo the contents of the "inserted" table
The "inserted" table contains the values of the newly inserted row
in tblDemoTrigger or the "after-image".
*/
SELECT 'inserted' AS Buffer, inserted.* FROM inserted
--Action...
INSERT INTO logPatients (PatientID, PatientNumber, FirstName, LastName, [Address], Phone, ChangedBy, ChangeTime, ChangeType, ImageType)
SELECT PatientID, PatientNumber, FirstName, LastName, [Address], Phone, LastChangedBy, GETDATE() AS ChangeTime, 'INSERT' AS ChangeType, 'AFTER' AS ImageType
FROM inserted
END
GO
CREATE TRIGGER trAuditPatients_Updates ON Patients
FOR UPDATE
AS
BEGIN
/*
Echo the contents of the "inserted" and "deleted" tables
The "inserted" table contains the after-image of the updated row.
The "deleted" table contains the before-image of the updated row.
*/
SELECT 'deleted' AS Buffer, deleted.*, 'before' AS RowImage FROM deleted
SELECT 'inserted' AS Buffer, inserted.*, 'after' AS RowImage FROM inserted
--Action...
INSERT INTO logPatients (PatientID, PatientNumber, FirstName, LastName, [Address], Phone, ChangedBy, ChangeTime, ChangeType, ImageType)
SELECT PatientID, PatientNumber, FirstName, LastName, [Address], Phone, LastChangedBy, GETDATE() AS ChangeTime, 'UPDATE' AS ChangeType, 'BEFORE' AS ImageType
FROM deleted
INSERT INTO logPatients (PatientID, PatientNumber, FirstName, LastName, [Address], Phone, ChangedBy, ChangeTime, ChangeType, ImageType)
SELECT PatientID, PatientNumber, FirstName, LastName, [Address], Phone, LastChangedBy, GETDATE() AS ChangeTime, 'UPDATE' AS ChangeType, 'AFTER' AS ImageType
FROM inserted
END
GO
CREATE TRIGGER trAuditPatients_Deletes ON Patients
FOR DELETE
AS
BEGIN
/*
Echo the contents of the "deleted" table
The "deleted" table contains the values of the deleted row.
*/
SELECT 'deleted' AS Buffer, deleted.*, 'BEFORE' AS RowImage FROM deleted
--Action...
INSERT INTO logPatients (PatientID, PatientNumber, FirstName, LastName, [Address], Phone, ChangedBy, ChangeTime, ChangeType, ImageType)
SELECT PatientID, PatientNumber, FirstName, LastName, [Address], Phone, LastChangedBy, GETDATE() AS ChangeTime, 'DELETE' AS ChangeType, 'BEFORE' AS ImageType
FROM deleted
END
GO
PRINT 'Audit Triggers successfully applied...'
GO
/* Test Data */
BEGIN TRY
BEGIN TRANSACTION
SELECT * FROM Patients
INSERT INTO dbo.Patients (PatientNumber, FirstName, LastName, [Address], Phone)
VALUES ('000001','John','Smith','123 Database BLVD, Computer City, AL 36600','251-251-2512'),
('000002','Margaret','Smith','123 Database BLVD, Computer City, AL 36600','251-251-2512'),
('000003','Peter','Smith','123 Database BLVD, Computer City, AL 36600','251-251-2512'),
('000004','Sally','Smith','123 Database BLVD, Computer City, AL 36600','251-251-2512'),
('000005','Alfred','Russel','200 Watchtower Lane, Mobile, AL 36608','251-444-2512'),
('000006','Alfreda','Russel','200 Watchtower Lane, Mobile, AL 36608','251-444-2512'),
('000007','Mark','Parker','888 Dr. Harold Pardue Drive, Mobile, AL 36608','251-444-0000')
UPDATE P1
SET GuarantorID = p2.PatientID
FROM dbo.Patients p1
JOIN dbo.Patients p2 ON p1.LastName = 'Smith' AND p2.LastName = 'Smith' AND p2.FirstName = 'John'
UPDATE P1
SET GuarantorID = p2.PatientID
FROM dbo.Patients p1
JOIN dbo.Patients p2 ON p1.LastName = 'Russel' AND p2.LastName = 'Russel' AND p2.FirstName = 'Alfred'
UPDATE P1
SET GuarantorID = p2.PatientID
FROM dbo.Patients p1
JOIN dbo.Patients p2 ON p1.LastName = 'Parker' AND p2.LastName = 'Parker' AND p2.FirstName = 'Mark'
INSERT INTO dbo.Providers (ProviderNumber, FirstName, LastName,[Address], Phone,Title)
VALUES ('500001','Juan','Garcia','100 Database BLVD, Computer City, AL 36600','251-851-2511','Hair Doctor'),
('500002','Luc','Moissette','100 Database BLVD, Computer City, AL 36600','851-251-2512','Nail Doctor'),
('500003','La Shaquita','Peoples','100 Database BLVD, Computer City, AL 36600','251-851-2513','Bug Doctor')
INSERT INTO dbo.PaymentTypes (Description)
VALUES ('Credit Card'),
('Bank Card'),
('Check'),
('Cash'),
('Electronic'),
('Insurance')
INSERT INTO dbo.MedicalProcedures (ProcedureCode, Description, Price)
VALUES ('H1111','Hair Removal',2400.00),
('N1112','Nail Surgery',1000.00),
('B2223','Bug Removal',2.50),
('H0005','Hair Surgery',25000.99),
('BNH01','Permanent Fix',250000.59)
INSERT INTO [OakridgeCentre].[dbo].[BillingStatements]
([AmountDue]
,[TotalItems]
,[TotalCharges]
,[TotalCredits]
,[AmountEnclosed]
,[Date]
,[Signature]
,[PatientID]
,[ProviderID])
VALUES
(100.50
,1
,200.10
,100
,0
,'02/12/2012'
,1
,1
,1)
GO
INSERT INTO [OakridgeCentre].[dbo].[BillingStatements]
([AmountDue]
,[TotalItems]
,[TotalCharges]
,[TotalCredits]
,[AmountEnclosed]
,[Date]
,[Signature]
,[PatientID]
,[ProviderID])
VALUES
(100.50
,1
,200.10
,100
,0
,'02/12/2012'
,1
,2
,1)
GO
INSERT INTO [OakridgeCentre].[dbo].[BillingStatements]
([AmountDue]
,[TotalItems]
,[TotalCharges]
,[TotalCredits]
,[AmountEnclosed]
,[Date]
,[Signature]
,[PatientID]
,[ProviderID])
VALUES
(100.50
,1
,200.10
,100
,0
,'02/12/2012'
,1
,2
,1)
GO
INSERT INTO dbo.Payments (PaymentTypeID, Amount, Date,PatientID)
SELECT PaymentTypeID , 100.00 ,'01-04-2011', PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Margaret' And Description = 'Credit Card'
UNION SELECT PaymentTypeID, 500.00,'01-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Margaret' And Description = 'Credit Card'
UNION SELECT PaymentTypeID,50.50,'01-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Sally' And Description = 'Credit Card'
UNION SELECT PaymentTypeID,300.00,'03-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Sally' And Description = 'Credit Card'
UNION SELECT PaymentTypeID,50.50,'01-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'John' And Description = 'Credit Card'
UNION SELECT PaymentTypeID,5.00,'01-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Margaret' And Description = 'Credit Card'
UNION SELECT PaymentTypeID,50.50,'01-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Peter' And Description = 'Credit Card'
UNION SELECT PaymentTypeID,500.00,'03-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Peter' And Description = 'Bank Card'
UNION SELECT PaymentTypeID,30.50,'03-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Sally' And Description = 'Bank Card'
UNION SELECT PaymentTypeID,500.00,'03-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Peter' And Description = 'Bank Card'
UNION SELECT PaymentTypeID,10.50,'03-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Sally' And Description = 'Bank Card'
UNION SELECT PaymentTypeID,800.00,'03-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Alfred' And Description = 'Credit Card'
UNION SELECT PaymentTypeID,6.50,'05-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Alfred' And Description = 'Insurance'
UNION SELECT PaymentTypeID,500.00,'11-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Alfred' And Description = 'Credit Card'
UNION SELECT PaymentTypeID,50.50,'03-21-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Margaret' And Description = 'Electronic'
UNION SELECT PaymentTypeID,700.00,'07-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Alfred' And Description = 'Credit Card'
UNION SELECT PaymentTypeID,2.00,'06-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Peter' And Description = 'Bank Card'
UNION SELECT PaymentTypeID,10.00,'04-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Peter' And Description = 'Bank Card'
UNION SELECT PaymentTypeID,50.50,'01-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Margaret' And Description = 'Bank Card'
UNION SELECT PaymentTypeID,10.00,'01-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Peter' And Description = 'Bank Card'
UNION SELECT PaymentTypeID,50.50,'01-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Alfred' And Description = 'Bank Card'
UNION SELECT PaymentTypeID,500.00,'03-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Peter' And Description = 'Bank Card'
UNION SELECT PaymentTypeID,30.50,'03-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Alfred' And Description = 'Cash'
UNION SELECT PaymentTypeID,500.00,'03-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Alfreda' And Description = 'Cash'
UNION SELECT PaymentTypeID,10.50,'03-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Alfreda' And Description = 'Cash'
UNION SELECT PaymentTypeID,800.00,'03-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Alfreda' And Description = 'Cash'
UNION SELECT PaymentTypeID,6.50,'05-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Alfreda' And Description = 'Cash'
UNION SELECT PaymentTypeID,500.00,'11-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Margaret' And Description = 'Cash'
UNION SELECT PaymentTypeID,50.50,'03-21-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Margaret' And Description = 'Cash'
UNION SELECT PaymentTypeID,700.00,'07-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Alfreda' And Description = 'Cash'
UNION SELECT PaymentTypeID,500.00,'06-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Margaret' And Description = 'Cash'
UNION SELECT PaymentTypeID,500.00,'04-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Margaret' And Description = 'Cash'
UNION SELECT PaymentTypeID,20.50,'08-14-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Peter' And Description = 'Insurance'
UNION SELECT PaymentTypeID,20.50,'08-14-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'John' And Description = 'Insurance'
UNION SELECT PaymentTypeID,50.50,'03-21-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Mark' And Description = 'Electronic'
UNION SELECT PaymentTypeID,700.00,'07-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Mark' And Description = 'Credit Card'
UNION SELECT PaymentTypeID,500.00,'11-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Margaret' And Description = 'Bank Card'
UNION SELECT PaymentTypeID,50.50,'03-21-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Margaret' And Description = 'Bank Card'
UNION SELECT PaymentTypeID,700.00,'07-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Alfreda' And Description = 'Cash'
UNION SELECT PaymentTypeID,500.00,'06-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Mark' And Description = 'Cash'
UNION SELECT PaymentTypeID,500.00,'04-04-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Margaret' And Description = 'Cash'
UNION SELECT PaymentTypeID,20.50,'08-14-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'Mark' And Description = 'Insurance'
UNION SELECT PaymentTypeID,20.50,'08-14-2011',PatientID FROM Patients, PaymentTypes WHERE FirstName = 'John' And Description = 'Insurance'
COMMIT TRANSACTION
PRINT 'Patients successfully inserted...'
END TRY
BEGIN CATCH
DECLARE @ErrorMessage VARCHAR(500)
SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Table Information insertions.'
ROLLBACK TRANSACTION
RAISERROR (@ErrorMessage, 16,1)
END CATCH
GO
CREATE VIEW vwPatientTotalPayments
AS
SELECT PatientNumber AS PatientAccount, PatientName = FirstName + ' ' + LastName , [Address] AS PatientAddress, TotalPayments
FROM (
SELECT PatientID, SUM(Amount) TotalPayments
FROM Payments
GROUP BY PatientID) AS x
JOIN Patients ON Patients.PatientID = x.PatientID
GO
CREATE VIEW vwGuarantorTotalPayments
AS
SELECT PatientNumber AS AccountNumber, GurantorName = FirstName + ' ' + LastName , [Address] AS GuarantorAddress, TotalPayments
FROM (
SELECT GuarantorID, SUM(Amount) TotalPayments
FROM Payments
JOIN Patients ON Payments.PatientID = Patients.PatientID
GROUP BY GuarantorID) AS x
JOIN Patients ON Patients.PatientID = x.GuarantorID
GO
CREATE VIEW vwPaymentTypesWithLeastAmount
AS
SELECT [Description] PaymentType, SUM(Amount) Total
FROM dbo.Payments
JOIN dbo.PaymentTypes ON Payments.PaymentTypeID =PaymentTypes.PaymentTypeID
GROUP BY [Description]
HAVING SUM(Amount) < 1000
GO
PRINT 'Successfully created views...'
GO
ALTER TRIGGER trigEnforceMinCardinality ON LineItems
FOR DELETE
AS
BEGIN
/*
Echo the contents of the "deleted" table
The "deleted" table contains the values of the deleted row.
*/
SELECT 'deleted' AS Buffer, deleted.*, 'BEFORE' AS RowImage FROM deleted
--Action...
IF (SELECT COUNT(*)
FROM BillingStatements
WHERE BillingStatementID = (SELECT DISTINCT BillingStatementID FROM deleted)) = 0
BEGIN
PRINT 'No Billing Statement was found'
COMMIT TRANSACTION
END
ELSE
IF (SELECT COUNT(*)
FROM LineItems
WHERE BillingStatementID = (SELECT DISTINCT BillingStatementID FROM deleted)) < 1
BEGIN
ROLLBACK TRAN
RAISERROR ('A billing statement must have at least one line item.', 16, 1)
END
END
GO
PRINT 'Successfully created trigger trigEnforceMinCardinality...'
GO
CREATE TRIGGER trigInsertBillingStatement ON BillingStatements
INSTEAD OF INSERT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION InsertBillingStatements
INSERT INTO BillingStatements (AmountDue, TotalItems, TotalCharges, TotalCredits, AmountEnclosed, [Date], [Signature],PatientID, ProviderID)
--VALUES (@SessionCode, @LcdProjectorRequired, @RoomID, @TimeSlotID, @TrackChairID, @EditorID);
SELECT AmountDue, TotalItems, TotalCharges, TotalCredits, AmountEnclosed, [Date], [Signature], PatientID, ProviderID FROM inserted;
DECLARE @BillingStatementID INT; SET @BillingStatementID = SCOPE_IDENTITY();
INSERT INTO LineItems (Quantity, [Date], BillingStatementID)
--VALUES (1, @PaperID, @SessionID);
SELECT NULL, NULL, @BillingStatementID FROM inserted;
COMMIT TRANSACTION InsertBillingStatements;
--Echo the contents of inserted for debugging
-- SELECT @SessionID AS SessionID, SessionCode, LcdProjectorRequired, RoomID, TimeSlotID, TrackChairID, EditorID, PaperID FROM inserted;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage VARCHAR(500);
SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: BillingStatement insertions.';
ROLLBACK TRANSACTION InsertBillingStatements;
RAISERROR (@ErrorMessage, 16,1);
END CATCH
END --INSTEAD OF trigger
GO
PRINT 'Successfully created INSTEAD OF trigger for BillingStatement...'
GO
February 24, 2012 at 7:43 am
You say that you are getting errors but you don't list what those errors are; it would be helpful to have those to give us some direction.
Also, how are the deletes being done? Are they being deleted one at a time or in batch? It sounds from your statements that several billing details are being deleted together, all for one patient. In that case, the "SELECT DISTINCT BillingStatementID FROM deleted" will give you more than one value, resulting in an error of "Subquery returned more than 1 value."
The data setup you provided is really good; thank you for that. Could you also provide a sample of a delete statement that is "failing"? That will give us something to test when trying a different trigger arrangement.
February 24, 2012 at 10:30 am
Thank you SO VERY MUCH! You are exactly right about the error message I'm getting. I fixed that one by replacing the = sign with the IN operator. But I'm still getting an error.
Msg 512, Level 16, State 1, Procedure trigEnforceMinCardinality, Line 14
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
This is the error I'm getting now
No Billing Statement was found
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
I don't understand what this means or why it is happening.
Here is the delete statement i'm testing with.
delete from BillingStatements where PatientID = 3
Here are the insert statements i'm testing with. THANKS A MILLION!!!
SET NOCOUNT ON
INSERT INTO [OakridgeCentre].[dbo].[BillingStatements]
([AmountDue]
,[TotalItems]
,[TotalCharges]
,[TotalCredits]
,[AmountEnclosed]
,[Date]
,[Signature]
,[PatientID]
,[ProviderID])
VALUES
(100.50
,1
,200.10
,100
,0
,'02/12/2012'
,1
,1
,1)
GO
INSERT INTO [OakridgeCentre].[dbo].[BillingStatements]
([AmountDue]
,[TotalItems]
,[TotalCharges]
,[TotalCredits]
,[AmountEnclosed]
,[Date]
,[Signature]
,[PatientID]
,[ProviderID])
VALUES
(100.50
,1
,200.10
,100
,0
,'02/12/2012'
,1
,2
,1)
GO
INSERT INTO [OakridgeCentre].[dbo].[BillingStatements]
([AmountDue]
,[TotalItems]
,[TotalCharges]
,[TotalCredits]
,[AmountEnclosed]
,[Date]
,[Signature]
,[PatientID]
,[ProviderID])
VALUES
(100.50
,1
,200.10
,100
,0
,'02/12/2012'
,1
,2
,1)
GO
February 24, 2012 at 2:01 pm
Well it helps to fix problems when you take out unnecessary COMMIT TRANSACTION!!!!!
Thanks guys! At least you started me to thinking.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply