daily pastebin goal
26%
SHARE
TWEET

Untitled

a guest Aug 12th, 2017 56 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top