daily pastebin goal
33%
SHARE
TWEET

Untitled

a guest Aug 12th, 2017 55 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TRIGGER tr32 ON wards
  2. INSTEAD OF DELETE
  3. AS
  4. BEGIN
  5.     DECLARE @counter int
  6.     DECLARE @sick int
  7.     DECLARE @temp TABLE (iid int, FNP varchar(60), ward_id int, diagnosis_id int)
  8.     DECLARE @wards TABLE (ward_id int)
  9.     DECLARE @diagnosis TABLE (diagnosis_id int)
  10.     DECLARE @peoples TABLE (iid int)
  11.  
  12.     INSERT INTO @temp (iid, FNP, ward_id, diagnosis_id)
  13.     SELECT iid, FNP, ward_id, diagnosis_id
  14.     FROM peoples
  15.     WHERE ward_id IN (SELECT iid
  16.                       FROM DELETED)
  17.  
  18.     INSERT INTO @diagnosis (diagnosis_id)
  19.     SELECT DISTINCT diagnosis_id
  20.     FROM @temp
  21.  
  22.     SET @counter = 0
  23.     SET @sick = 0
  24.  
  25.     INSERT INTO @wards (ward_id)
  26.     SELECT DISTINCT ward_id
  27.     FROM peoples
  28.     WHERE diagnosis_id IN (SELECT DISTINCT diagnosis_id
  29.                            FROM peoples
  30.                            WHERE ward_id IN (SELECT iid
  31.                                              FROM DELETED))
  32.     EXCEPT
  33.     SELECT iid
  34.     FROM DELETED
  35.  
  36.     WHILE((SELECT COUNT(*)
  37.            FROM @temp) > 0)
  38.     BEGIN
  39.         IF((SELECT COUNT(*)
  40.             FROM @wards) > 0)
  41.         BEGIN
  42.             SET @sick = (SELECT TOP 1 diagnosis_id
  43.                          FROM @diagnosis)
  44.             WHILE((SELECT COUNT(*)
  45.                    FROM @temp
  46.                    WHERE diagnosis_id = @sick) > 0)
  47.             BEGIN
  48.                 SET @counter = (SELECT TOP 1 ward_id
  49.                                 FROM peoples
  50.                                 WHERE diagnosis_id = @sick
  51.                                 AND ward_id IN (SELECT ward_id
  52.                                                 FROM @wards))
  53.                 IF((SELECT COUNT(*)
  54.                     FROM peoples
  55.                     WHERE ward_id = @counter) < (SELECT max_count
  56.                                                  FROM wards
  57.                                                  WHERE iid = @counter))
  58.                 BEGIN
  59.                     INSERT INTO @peoples (iid)
  60.                     SELECT TOP ((SELECT max_count
  61.                                  FROM wards
  62.                                  WHERE iid = @counter) - (SELECT COUNT(*)
  63.                                                           FROM peoples
  64.                                                           WHERE ward_id = @counter)) iid
  65.                     FROM @temp
  66.                     WHERE diagnosis_id = @sick
  67.  
  68.                     UPDATE peoples
  69.                     SET ward_id = @counter
  70.                     WHERE iid IN (SELECT *
  71.                                   FROM @peoples)
  72.                     DELETE FROM @temp
  73.                     WHERE iid IN (SELECT *
  74.                                   FROM @peoples)
  75.                     DELETE FROM @peoples
  76.                 END
  77.                 ELSE
  78.                     DELETE FROM @wards
  79.                     WHERE ward_id = @counter
  80.             END
  81.             IF((SELECT COUNT(*)
  82.                 FROM peoples
  83.                 WHERE ward_id = @counter) = (SELECT max_count
  84.                                              FROM wards
  85.                                              WHERE iid = @counter))
  86.                 DELETE FROM @wards
  87.                 WHERE ward_id = @counter
  88.             IF((SELECT COUNT(*)
  89.                 FROM @temp
  90.                 WHERE diagnosis_id = @sick) = 0)
  91.                 DELETE FROM @diagnosis
  92.                 WHERE diagnosis_id = @sick
  93.         END
  94.         ELSE
  95.             ROLLBACK TRAN
  96.     END
  97.     DELETE FROM wards
  98.     WHERE iid IN (SELECT iid
  99.                   FROM DELETED)
  100. END
RAW Paste Data
Top