guQZinWc

1DROP TABLE IF EXISTS foo;
2CREATE TABLE FOO (
3  i SERIAL PRIMARY KEY
4);
5
6DELIMITER //
7DROP TRIGGER IF EXISTS bar //
8
9CREATE TRIGGER bar AFTER INSERT ON foo
10FOR EACH ROW BEGIN
11  DECLARE x INT;
12  SET x = NEW.i;
13  SET @a = x; -- set user variable outside trigger
14END//
15
16DELIMITER ;
17
18SET @a = 0;
19
20SELECT @a; -- returns 0
21
22INSERT INTO foo () VALUES ();
23
24SELECT @a; -- returns 1, the value it got during the trigger
25	
26CREATE TRIGGER bar AFTER INSERT ON foo
27FOR EACH ROW BEGIN
28  DECLARE x INT;
29  SET x = (SELECT age FROM users WHERE name = 'Bill'); 
30  -- ERROR 1242 if more than one row with 'Bill'
31END//
32
33CREATE TRIGGER bar AFTER INSERT ON foo
34FOR EACH ROW BEGIN
35  DECLARE x INT;
36  SET x = (SELECT MAX(age) FROM users WHERE name = 'Bill');
37  -- OK even when more than one row with 'Bill'
38END//
39	
40`CREATE TRIGGER `category_before_ins_tr` BEFORE INSERT ON `category`
41  FOR EACH ROW
42BEGIN
43    **SET @tableId= (SELECT id FROM dummy LIMIT 1);**
44
45END;`;
46	
47CREATE TRIGGER clearcamcdr AFTER INSERT ON `asteriskcdrdb`.`cdr` 
48FOR EACH ROW
49BEGIN
50  SET @INC = (SELECT sip_inc FROM trunks LIMIT 1);
51  IF NEW.billsec >1 AND NEW.channel LIKE @INC 
52    AND NEW.dstchannel NOT LIKE "" 
53  THEN
54    insert into `asteriskcdrdb`.`filtre` (id_appel,date_appel,source,destinataire,duree,sens,commentaire,suivi) 
55      values (NEW.id,NEW.calldate,NEW.src,NEW.dstchannel,NEW.billsec,"entrant","",""); 
56  END IF;
57END$$
58	
59BEGIN
60  -- prevent duplicate composite keys when merging in archive to main
61  SET @EXIST_COMPOSITE_KEY = (SELECT count(*) FROM patientrecords where rridprefix = NEW.rridprefix and rrid = NEW.rrid);
62  -- if the composite key to be introduced during merge exists, "juggle"
63  IF @EXIST_COMPOSITE_KEY > 0
64  THEN
65
66    -- THIS IS WHAT I NEEDED
67    -- set the incoming column data this way (if composite key exists)
68
69    -- the legacy duplicate rrid field will help us keep the bad data
70    SET NEW.legacyduperrid = NEW.rrid;
71
72    -- allow the following block to set the new rrid appropriately
73    SET NEW.rrid = null;
74
75  END IF;
76
77  -- legacy code tried set the rrid (race condition), now the db does it
78  SET NEW.rrid = (
79    -- SELECT if(NEW.legacyduperrid is not null, IFNULL(MAX(rrid), 0) + 1, null)
80    SELECT if(NEW.rrid is null and NEW.legacyduperrid is null, IFNULL(MAX(rrid), 0) + 1, NEW.rrid)
81    FROM patientrecords
82    WHERE rridprefix  = NEW.rridprefix
83  );
84END