0

I Have 3 Tables:

1) CREATE TABLE `UrunPaketDetay`(
    `UrunPaketDetay` int NOT NULL AUTO_INCREMENT, 
    `UrunPaketNo` int NULL, 
    `Tarih` date NULL, 
    `Barkod` varchar(14) NOT NULL, 
    `Urun` varchar(50) NULL, 
    `SunumSekli` varchar(50) NULL, 
    `IslemeSekli` varchar(50) NULL, 
    `PaketlemeSekli` varchar(50) NULL, 
    `Kalibre` varchar(50) NULL, 
    `Kilo` double NULL, 
    `GlazeOran` varchar(50) NULL, 
    `Uretici` varchar(190) NULL, 
    `PaletKod` varchar(50) NULL, 
    PRIMARY KEY (`UrunPaketDetay`)
)CHARACTER SET utf8;

2) CREATE TABLE `CkisEks`(
    `CikId` int NOT NULL AUTO_INCREMENT, 
    `Tarih` date NULL, 
    `Musteri` varchar(190) NULL, 
    `TeslimatYer` varchar(50) NULL, 
    `CikisSaati` time NULL, 
    `AracPlakasi` varchar(18) NULL, 
    `AracTel` varchar(16) NULL, 
    `KonteynirNo` varchar(50) NULL, 
    `PaletKod` varchar(12) NULL, 
    `Kilo` double NULL, 
    PRIMARY KEY (`CikId`)
)CHARACTER SET utf8;

3) CREATE TABLE `Ckis_Detay`(
    `CD_Id` int NOT NULL AUTO_INCREMENT, 
    `CikId` int NULL, 
    `Barkod` varchar(50) NULL, 
    `Urun` varchar(50) NULL, 
    `Kalibre` varchar(50) NULL, 
    `Kilo` double NULL, 
    `Uretici` varchar(50) NULL, 
    `Musteri` varchar(190) NULL, 
    `PaletKod` varchar(50) NULL, 
    `Tarih` date NULL, 
    PRIMARY KEY (`CD_Id`)
)CHARACTER SET utf8;

My Question:

I fill up my first table. After on 2nd table i call PaletKod row. PaletKod row autofill Kilo Row on 2nd table from 1st Table.

But i need when i fill PaletKod to my 2nd table how can i select Paletkod from first table and fill my 3rd table values (Uretici, Urun, Kilo, Kalibre ) ?

With Trigger ?

  • You want simply copy the data, inserted to table 1, into tables 2 and 3, to all records in that tables with the same PaletKod field value? – Akina Aug 28 '18 at 13:32
  • i want to copy Uretici, Urun, Kilo, Kalibre rows to 3rd table matching "PaletKod" – Taz Plus Aug 28 '18 at 13:34
  • Mmm... You do NOT want to copy values when data is inserted into table 1? you want to do it only when values are copied into table2? – Akina Aug 28 '18 at 13:38
  • @Akina because i need report page on table 3. so when i save 2nd table with PaletKod field after copy matching paletkod data 1st table to 3rd table. – Taz Plus Aug 28 '18 at 13:40
  • If so - yes, use trigger AFTER INSERT and/or AFTER UPDATE on table 2. But I'd recommend to use stored procedure which inserts/updates data into table 2 (and table 3) instead of single query + trigger... especially if you need to do it in a transaction. – Akina Aug 28 '18 at 13:43
0
create trigger tr_au_CkisEks
after update on CkisEks
for each row
update UrunPaketDetay t1, Ckis_Detay t2
set t2.Uretici = t1.Uretici, 
    t2.Urun = t1.Urun, 
    t2.Kilo = t1.Kilo, 
    t2.Kalibre = t1.Kalibre
where t1.PaletKod = NEW.PaletKod
  and t2.PaletKod = NEW.PaletKod;
  • this trigger update ? is it need after insert ? – Taz Plus Aug 28 '18 at 19:51
  • @TazPlus Depends of a logic you need. Additionally remember the record in Ckis_Detay you must update may not exist - so you must check the amount of relevant records and, if it is 0, insert instead of update. – Akina Aug 29 '18 at 4:41
  • data will be insert first time to Ckis_Detay so i need insert trigger – Taz Plus Aug 29 '18 at 9:27
  • i code this BEGIN INSERT INTO Ckis_Detay (PaletKod, Barkod, Urun, Kalibre, Kilo, Uretici) SELECT PaletKod, Barkod, Urun, Kalibre, Kilo, Uretici FROM UrunPaketDetay WHERE PaletKod = PaletKod; END But i need only same paletkod save to table 3 how can i ? – Taz Plus Aug 29 '18 at 11:15
  • @TazPlus WHERE PaletKod = NEW.PaletKod. And remember, that there can be more than 1 record with the same PaletKod in UrunPaketDetay table and, as an effect, inserting more than 1 record into Ckis_Detay. – Akina Aug 29 '18 at 11:20
0
BEGIN

INSERT INTO Ckis_Detay (PaletKod, Barkod, Urun, Kalibre, Kilo, Uretici) SELECT PaletKod, Barkod, Urun, Kalibre, Kilo, Uretici FROM UrunPaketDetay WHERE PaletKod = PaletKod; END

i code this trigger but when i save table 2 this trigger get all data from table 1 to table 3 But i just need only same PaletKod data copy

  • Do not publish additional info as an answer. Insert it to the end of your question as its UPDATE. – Akina Aug 29 '18 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.