codeBloger codeBloger - 7 months ago 17
SQL Question

Last Id Not Detected

I am trying to create a trigger .Like this

CREATE TABLE IF NOT EXISTS `tbl_a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`villa` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `tbl_a`
--

INSERT INTO `tbl_a` (`id`, `name`, `villa`) VALUES
(1, 'KAKUL', 'ASHOK NAGER');

--
-- Triggers `tbl_a`
--
DROP TRIGGER IF EXISTS `test`;
DELIMITER //
CREATE TRIGGER `test` BEFORE UPDATE ON `tbl_a`
FOR EACH ROW INSERT INTO tbl_b_bckup(a_id,name,villa) SELECT (id,name,villa) FROM tbl_a WHERE id = @id
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `tbl_b_bckup`
--

CREATE TABLE IF NOT EXISTS `tbl_b_bckup` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a_id` int(11) NOT NULL,
`name` varchar(30) NOT NULL,
`villa` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


If i replace @id with no its works but . i need last updated rows_id . How can i get it?
I want that Before update any thing on tbl_a . A backup row will be create on tbl_b_backup .
This query not even allow me to edit the tbl_a

Answer
CREATE TRIGGER `test` 
BEFORE UPDATE ON `tbl_a`
FOR EACH ROW 
INSERT INTO tbl_b_bckup(a_id,name,villa)
VALUES (OLD.id,OLD.name,OLD.villa)

There is no need to select the row from tbl_a. You already have it in a pseudo-row known by the alias OLD.