Help with Triggers - SQL Server Forums

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

Help with Triggers

Author

Topic

cm2adams
Starting Member

6 Posts

Posted - 2009-07-11 : 12:07:48

Hi,

I'm pretty new to SQL programming so bear with me. I'm trying to create a few related triggers and have no idea how to go about it. I have two tables. One with the primary key and the other with the foreign key. What I'm trying to do is limit the number of rows in in the foreign key table to a maximum of 6 for each item in the primary key table. This is for a billing software where each patient will have no more that 6 claims/visit.

The second thing i need to do is automatically number them from 1 to 6 in a separate column.

The third thing is to renumber them if I delete an item so that for example if I delete the 3rd of 6 items, they get renumberd 1,2,3,4,5 as opposed to 1,2,4,5,6.

Table1_Visits
Columns
pk_visitID
patients
etc...

Table2_Claims
Columns
fk_visitID
Item1to6
etc...

Any help would be appreciated.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-12 : 03:44:39

seems like you need a trigger like this


CREATE TRIGGER CheckValues
ON Table2_Claims
AFTER INSERT,DELETE
AS
BEGIN
IF EXISTS(SELECT 1 FROM DELETED)
BEGIN
 UPDATE t
 SET t.Item1to6=t.Item1to6-(SELECT COUNT(*) FROM DELETED WHERE fk_visitID=t.fk_visitID AND d.PKCol<t.PKCol)
 FROM Table2_Claims t
  
END
IF EXISTS(SELECT 1 FROM INSERTED)
BEGIN
 IF ((SELECT COUNT(*) FROM Table2_Claims  t
     WHERE EXISTS(SELECT 1 FROM INSERTED WHERE fk_visitID=t.fk_visitID))>6)
 BEGIN
    RAISERROR ('There can be only max 6 items linked to visit ID',16,1)
    ROLLBACK TRANSACTION 
 END
ELSE
  UPDATE t
  SET t.Item1to6=(SELECT COUNT(*) FROM Table2_Claims WHERE fk_visitID=t.fk_visitID AND d.PKCol<t.PKCol)+1
 FROM Table2_Claims t
 INNER JOIN INSERTED i
 ON i.PKCol=t.PKCol
  
END
END

PK col is primary key of your claims table

cm2adams
Starting Member

6 Posts

Posted - 2009-07-12 : 23:39:18

That was excellent! I modified one line slightly.


  SET t.Item1to6=(SELECT COUNT(*) FROM Table2_Claims WHERE fk_visitID=t.fk_visitID AND i.PKCol=t.PKCol)

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 13:45:25

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources