INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

debugging trigger

debugging trigger

(OP)
I am trying to write a trigger, but nothing is getting updated. The insert portion works fine. Any advice is greatly appreciated. Thanks in advance.

CODE --> SQL

DROP TABLE IF EXISTS `collectionsmax`.`payments_timestamp`;
CREATE TABLE  `collectionsmax`.`payments_timestamp` (
  `PaymentsID` int(10) unsigned NOT NULL,
  `PaymentAmount` decimal(10) unsigned NOT NULL,
  `LastUpdated` datetime NOT NULL,
  `AssignedTo` varchar(200) NOT NULL,
  `ActionType` varchar(45) NOT NULL,
  `PaymentDate` varchar(25) NOT NULL,
  `EnteredBy` varchar(200) NOT NULL,
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FileNumber` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=175761 DEFAULT CHARSET=latin1;


DELIMITER $$

USE `collectionsmax`$$

DROP TRIGGER /*!50032 IF EXISTS */ `InsertPaymentTimestamp`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `InsertPaymentTimestamp` AFTER INSERT ON `payments`
    FOR EACH ROW BEGIN
    INSERT INTO collectionsmax.payments_timestamp (PaymentsID, PaymentAmount,AssignedTo, ActionType, LastUpdated, PaymentDate, EnteredBy,FileNumber)
    VALUES(NEW.ID,NEW.PaymentAmount, NEW.AssignedTo,New.PaymentStatus,NOW(),New.PaymentDateFormat,Left(USER(),LOCATE('@',USER())-1),New.FileNumber);

  END;
$$

DELIMITER ;


DELIMITER $$

USE `collectionsmax`$$

DELIMITER $$

USE `collectionsmax`$$

DROP TRIGGER /*!50032 IF EXISTS */ `UpdatePaymentTimestamp`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `UpdatePaymentTimestamp` BEFORE UPDATE ON `payments`
    FOR EACH ROW BEGIN


        UPDATE collectionsmax.payments_timestamp
        SET ActionType = NEW.PaymentStatus, PaymentAmount = NEW.PaymentAmount, LastUpdated = NOW(), PaymentDate = NEW.PaymentDateFormat
        WHERE PaymentsID = OLD.ID AND FileNumber = OLD.FileNumber AND PaymentDate = OLD.PaymentDateFormat AND ActionType = OLD.PaymentStatus
		and PaymentAmount = OLD.PaymentAmount;



  END;
$$

DELIMITER ; 


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close