Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Enforcing Minimal Cardinality with Triggers

Author  Topic 

JacobPressures
Posting Yak Master

112 Posts
Posted - 2012-02-24 : 00:12:09
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. You can run this and it will set up the whole db structure. but the above is the only part I'm worried about



/* 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
(
 AuditLogID		INT IDENTITY(1,1) PRIMARY KEY,
 PatientID      INT,   
 PatientNumber  CHAR(6),    
 FirstName      VARCHAR(30),
 LastName       VARCHAR(30),
 [Address]      VARCHAR(100),
 Phone          CHAR(12),
 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

-- 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
(
 AuditLogID		INT 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),
 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

-- 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
(
 AuditLogID		INT IDENTITY(1,1) PRIMARY KEY,
 PaymentTypeID  INT,
 [Description]  VARCHAR(30),
 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 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
(
 AuditLogID			INT IDENTITY(1,1) PRIMARY KEY,
 BillingStatementID INT,
 AmountDue          MONEY,   
 TotalItems         INT,
 TotalCharges       MONEY,
 TotalCredits       MONEY,
 AmountEnclosed     MONEY,
 [Date]             DATETIME,
 [Signature]        BIT,
 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 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
(
 AuditLogID		INT IDENTITY(1,1) PRIMARY KEY,
 LineItemID     INT,
 [Date]         MONEY,  
 Quantity       INT,
 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 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 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

JacobPressures
Posting Yak Master

112 Posts
Posted - 2012-02-24 : 16:03:18
Ok, I've solved it. It was a couple of simple mistakes that I simply need to pay more attention to. I needed an IN operator rather than an = sign. I also needed to get rid of the COMMIT TRANSACTION which was unnecessary given that i didn't begin one.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site