======UPDATE TRIGGER===========

delimiter //
DROP TRIGGER item_u;
delimiter //
CREATE TRIGGER item_u
AFTER UPDATE ON item
FOR EACH ROW
BEGIN
UPDATE doit b SET b.invoice='',b.it='',status='' WHERE b.smit=OLD.smit;
UPDATE doit b set b.invoice=NEW.invoice,b.it=NEW.item,status='$' WHERE b.smit=NEW.smit;
UPDATE n3case d SET d.invoice=NEW.invoice,d.it=NEW.item WHERE d.smit=NEW.smit;
UPDATE invoice a
   SET a.cases = 
    (SELECT SUM(cases) 
       FROM item
      WHERE invoice = a.invoice),
   	a.amount=
    (SELECT SUM(amount)
       FROM item
      WHERE invoice = a.invoice)
 WHERE a.invoice = NEW.invoice;
END;//
=============INSERT TRIGGER==============

delimiter //
DROP TRIGGER item_i;
delimiter //
CREATE TRIGGER item_i
AFTER INSERT ON item
FOR EACH ROW
BEGIN
UPDATE doit b set b.invoice=NEW.invoice,b.it=NEW.item,status='$' WHERE b.smit=NEW.smit;
UPDATE n3case d SET d.invoice=NEW.invoice,d.it=NEW.item WHERE d.smit=NEW.smit;
UPDATE invoice a
   SET a.cases = 
    (SELECT SUM(cases) 
       FROM item
      WHERE invoice = a.invoice),
   	a.amount=
    (SELECT SUM(amount)
       FROM item
     WHERE invoice = a.invoice)
 WHERE a.invoice = NEW.invoice;

END;//

============DELETE TRIGGER==============

delimiter //
DROP TRIGGER item_d;
CREATE TRIGGER item_d
AFTER DELETE ON item
FOR EACH ROW
BEGIN
UPDATE invoice a
   SET a.cases = 
    (SELECT SUm(cases)
       FROM item
      WHERE invoice = a.invoice),
	a.amount=
    (SELECT SUM(amount)
       FROM item
      WHERE invoice = a.invoice)
WHERE a.invoice = OLD.invoice;
UPDATE doit b SET b.status='',b.invoice='',b.it='' WHERE b.smit=OLD.smit;
UPDATE n3case d SET d.invoice='',d.it='' WHERE d.smit=OLD.smit;
END;//

**********************DOIT TRIGGERS***************************

***************UPDATE DOIT TRIGGER******************

****should be able to combine the updates somehow
delimiter //
DROP TRIGGER doit_u;
CREATE TRIGGER doit_u
AFTER UPDATE on doit
FOR EACH ROW
BEGIN
UPDATE doship c SET c.status= (SELECT MIN(LENGTH(doit.status)) FROM doit WHERE c.sm = doit.sm);
UPDATE doship c SET c.status= if(c.status='1','$','');
END;//