~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

(BEFORE_INSERT: new=(id=1, data=3))(BEFORE_UPDATE: old=(id=1, data=1) new=(id=1, data=2))(AFTER_UPDATE: old=(id=1, data=1) new=(id=1, data=2))(BEFORE_INSERT: new=(id=2, data=2))(AFTER_INSERT: new=(id=2, data=2))

176

set @log:= "";

177

replace t1 values (1, 4), (3, 3);

178

select @log;

179

@log

180

(BEFORE_INSERT: new=(id=1, data=4))(BEFORE_DELETE: old=(id=1, data=2))(AFTER_DELETE: old=(id=1, data=2))(AFTER_INSERT: new=(id=1, data=4))(BEFORE_INSERT: new=(id=3, data=3))(AFTER_INSERT: new=(id=3, data=3))

181

drop trigger t1_bd;

182

drop trigger t1_ad;

183

set @log:= "";

184

replace t1 values (1, 5);

185

select @log;

186

@log

187

(BEFORE_INSERT: new=(id=1, data=5))(AFTER_INSERT: new=(id=1, data=5))

188

drop table t1;

189

create table t1 (id int primary key, data varchar(10), fk int);

190

create table t2 (event varchar(100));

191

create table t3 (id int primary key);

192

create trigger t1_ai after insert on t1 for each row

193

insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "'"));

194

insert into t1 (id, data) values (1, "one"), (2, "two");

195

select * from t1;

196

id data fk

197

1 one NULL

198

2 two NULL

199

select * from t2;

200

event

201

INSERT INTO t1 id=1 data='one'

202

INSERT INTO t1 id=2 data='two'

203

drop trigger t1_ai;

204

create trigger t1_bi before insert on t1 for each row

205

begin

206

if exists (select id from t3 where id=new.fk) then

207

insert into t2 values (concat("INSERT INTO t1 id=", new.id, " data='", new.data, "' fk=", new.fk));

208

else

209

insert into t2 values (concat("INSERT INTO t1 FAILED id=", new.id, " data='", new.data, "' fk=", new.fk));

210

set new.id= NULL;

211

end if;

212

end|

213

insert into t3 values (1);

214

insert into t1 values (4, "four", 1), (5, "five", 2);

215

ERROR 23000: Column 'id' cannot be null

216

select * from t1;

217

id data fk

218

1 one NULL

219

2 two NULL

220

4 four 1

221

select * from t2;

222

event

223

INSERT INTO t1 id=1 data='one'

224

INSERT INTO t1 id=2 data='two'

225

INSERT INTO t1 id=4 data='four' fk=1

226

INSERT INTO t1 FAILED id=5 data='five' fk=2

227

drop table t1, t2, t3;

228

create table t1 (id int primary key, data varchar(10));

229

create table t2 (seq int);

230

insert into t2 values (10);

231

create function f1 () returns int return (select max(seq) from t2);

232

create trigger t1_bi before insert on t1 for each row

233

begin

234

if new.id > f1() then

235

set new.id:= f1();

236

end if;

237

end|

238

insert into t1 values (1, "first");

239

insert into t1 values (f1(), "max");

240

select * from t1;

241

id data

242

1 first

243

10 max

244

drop table t1, t2;

245

drop function f1;

246

create table t1 (id int primary key, fk_t2 int);

247

create table t2 (id int primary key, fk_t3 int);

248

create table t3 (id int primary key);

249

insert into t1 values (1,1), (2,1), (3,2);

250

insert into t2 values (1,1), (2,2);

251

insert into t3 values (1), (2);

252

create trigger t3_ad after delete on t3 for each row

253

delete from t2 where fk_t3=old.id;

254

create trigger t2_ad after delete on t2 for each row

255

delete from t1 where fk_t2=old.id;

256

delete from t3 where id = 1;

257

select * from t1 left join (t2 left join t3 on t2.fk_t3 = t3.id) on t1.fk_t2 = t2.id;

258

id fk_t2 id fk_t3 id

259

3 2 2 2 2

260

drop table t1, t2, t3;

261

create table t1 (id int primary key, copy int);

262

create table t2 (id int primary key, data int);

263

insert into t2 values (1,1), (2,2);

264

create trigger t1_bi before insert on t1 for each row

265

set new.copy= (select data from t2 where id = new.id);

266

create trigger t1_bu before update on t1 for each row

267

set new.copy= (select data from t2 where id = new.id);

268

insert into t1 values (1,3), (2,4), (3,3);

269

update t1 set copy= 1 where id = 2;

270

select * from t1;

271

id copy

272

1 1

273

2 2

274

3 NULL

275

drop table t1, t2;

276

create table t1 (i int);

277

create table t3 (i int);

278

create trigger trg before insert on t1 for each row set @a:= old.i;

279

ERROR HY000: There is no OLD row in on INSERT trigger

280

create trigger trg before delete on t1 for each row set @a:= new.i;

281

ERROR HY000: There is no NEW row in on DELETE trigger

282

create trigger trg before update on t1 for each row set old.i:=1;

283

ERROR HY000: Updating of OLD row is not allowed in trigger

284

create trigger trg before delete on t1 for each row set new.i:=1;

285

ERROR HY000: There is no NEW row in on DELETE trigger

286

create trigger trg after update on t1 for each row set new.i:=1;

287

ERROR HY000: Updating of NEW row is not allowed in after trigger

288

create trigger trg before update on t1 for each row set new.j:=1;

289

ERROR 42S22: Unknown column 'j' in 'NEW'

290

create trigger trg before update on t1 for each row set @a:=old.j;

291

ERROR 42S22: Unknown column 'j' in 'OLD'

292

create trigger trg before insert on t2 for each row set @a:=1;

293

ERROR 42S02: Table 'test.t2' doesn't exist

294

create trigger trg before insert on t1 for each row set @a:=1;

295

create trigger trg after insert on t1 for each row set @a:=1;

296

ERROR HY000: Trigger already exists

297

create trigger trg2 before insert on t1 for each row set @a:=1;

298

ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

299

create trigger trg before insert on t3 for each row set @a:=1;

300

ERROR HY000: Trigger already exists

301

create trigger trg2 before insert on t3 for each row set @a:=1;

302

drop trigger trg2;

303

drop trigger trg;

304

drop trigger trg;

305

ERROR HY000: Trigger does not exist

306

create view v1 as select * from t1;

307

create trigger trg before insert on v1 for each row set @a:=1;

308

ERROR HY000: 'test.v1' is not BASE TABLE

309

drop view v1;

310

drop table t1;

311

drop table t3;

312

create temporary table t1 (i int);

313

create trigger trg before insert on t1 for each row set @a:=1;

314

ERROR HY000: Trigger's 't1' is view or temporary table

315

drop table t1;

316

create table t1 (x1col char);

317

create trigger tx1 before insert on t1 for each row set new.x1col = 'x';

318

insert into t1 values ('y');

319

drop trigger tx1;

320

drop table t1;

321

create table t1 (i int) engine=myisam;

322

insert into t1 values (1), (2);

323

create trigger trg1 before delete on t1 for each row set @del_before:= @del_before + old.i;

324

create trigger trg2 after delete on t1 for each row set @del_after:= @del_after + old.i;

325

set @del_before:=0, @del_after:= 0;

326

delete from t1;

327

select @del_before, @del_after;

328

@del_before @del_after

329

3 3

330

drop trigger trg1;

331

drop trigger trg2;

332

drop table t1;

333

create table t1 (a int);

334

create trigger trg1 before insert on t1 for each row set new.a= 10;

335

drop table t1;

336

create table t1 (a int);

337

insert into t1 values ();

338

select * from t1;

339

340

NULL

341

drop table t1;

342

create database mysqltest;

343

use mysqltest;

344

create table t1 (i int);

345

create trigger trg1 before insert on t1 for each row set @a:= 1;

346

drop database mysqltest;

347

use test;

348

create database mysqltest;

349

create table mysqltest.t1 (i int);

350

create trigger trg1 before insert on mysqltest.t1 for each row set @a:= 1;

351

ERROR HY000: Trigger in wrong schema

352

use mysqltest;

353

create trigger test.trg1 before insert on t1 for each row set @a:= 1;

354

ERROR 42S02: Table 'test.t1' doesn't exist

355

drop database mysqltest;

356

use test;

357

create table t1 (i int, j int default 10, k int not null, key (k));

358

create table t2 (i int);

359

insert into t1 (i, k) values (1, 1);

360

insert into t2 values (1);

361

create trigger trg1 before update on t1 for each row set @a:= @a + new.j - old.j;

362

create trigger trg2 after update on t1 for each row set @b:= "Fired";

363

set @a:= 0, @b:= "";

364

update t1, t2 set j = j + 10 where t1.i = t2.i;

365

select @a, @b;

366

@a @b

367

10 Fired

368

insert into t1 values (2, 13, 2);

369

insert into t2 values (2);

370

set @a:= 0, @b:= "";

371

update t1, t2 set j = j + 15 where t1.i = t2.i and t1.k >= 2;

372

select @a, @b;

373

@a @b

374

15 Fired

375

create trigger trg3 before delete on t1 for each row set @c:= @c + old.j;

376

create trigger trg4 before delete on t2 for each row set @d:= @d + old.i;

377

create trigger trg5 after delete on t1 for each row set @e:= "After delete t1 fired";

378

create trigger trg6 after delete on t2 for each row set @f:= "After delete t2 fired";

379

set @c:= 0, @d:= 0, @e:= "", @f:= "";

380

delete t1, t2 from t1, t2 where t1.i = t2.i;

381

select @c, @d, @e, @f;

382

@c @d @e @f

383

48 3 After delete t1 fired After delete t2 fired

384

drop table t1, t2;

385

create table t1 (i int, j int default 10)|

386

create table t2 (i int)|

387

insert into t2 values (1), (2)|

388

create trigger trg1 before insert on t1 for each row

389

begin

390

if new.i = 1 then

391

set new.j := 1;

392

end if;

393

end|

394

create trigger trg2 after insert on t1 for each row set @a:= 1|

395

set @a:= 0|

396

insert into t1 (i) select * from t2|

397

select * from t1|

398

i j

399

1 1

400

2 10

401

select @a|

402

403

404

drop table t1, t2|

405

create table t1 (i int, j int, k int);

406

create trigger trg1 before insert on t1 for each row set new.k = new.i;

407

create trigger trg2 after insert on t1 for each row set @b:= "Fired";

408

set @b:="";

409

load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, i);

410

select *, @b from t1;

411

i j k @b

412

10 NULL 10 Fired

413

15 NULL 15 Fired

414

set @b:="";

415

load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, j);

416

select *, @b from t1;

417

i j k @b

418

10 NULL 10 Fired

419

15 NULL 15 Fired

420

1 2 1 Fired

421

3 4 3 Fired

422

5 6 5 Fired

423

drop table t1;

424

create table t1 (i int, at int, k int, key(k)) engine=myisam;

425

create table t2 (i int);

426

insert into t1 values (1, 1, 1);

427

insert into t2 values (1), (2), (3);

428

create trigger ai after insert on t1 for each row set @a:= new.at;

429

create trigger au after update on t1 for each row set @a:= new.at;

430

create trigger ad after delete on t1 for each row set @a:= old.at;

431

alter table t1 drop column at;

432

select * from t1;

433

i k

434

1 1

435

insert into t1 values (2, 1);

436

ERROR 42S22: Unknown column 'at' in 'NEW'

437

select * from t1;

438

i k

439

1 1

440

2 1

441

update t1 set k = 2 where i = 2;

442

ERROR 42S22: Unknown column 'at' in 'NEW'

443

select * from t1;

444

i k

445

1 1

446

2 2

447

delete from t1 where i = 2;

448

ERROR 42S22: Unknown column 'at' in 'OLD'

449

select * from t1;

450

i k

451

1 1

452

load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k);

453

ERROR 42S22: Unknown column 'at' in 'NEW'

454

select * from t1;

455

i k

456

1 1

457

1 2

458

insert into t1 select 3, 3;

459

ERROR 42S22: Unknown column 'at' in 'NEW'

460

select * from t1;

461

i k

462

1 1

463

1 2

464

3 3

465

update t1, t2 set k = k + 10 where t1.i = t2.i;

466

ERROR 42S22: Unknown column 'at' in 'NEW'

467

select * from t1;

468

i k

469

1 11

470

1 2

471

3 3

472

update t1, t2 set k = k + 10 where t1.i = t2.i and k < 3;

473

ERROR 42S22: Unknown column 'at' in 'NEW'

474

select * from t1;

475

i k

476

1 11

477

1 12

478

3 3

479

delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;

480

ERROR 42S22: Unknown column 'at' in 'OLD'

481

select * from t1;

482

i k

483

1 12

484

3 3

485

delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;

486

ERROR 42S22: Unknown column 'at' in 'OLD'

487

select * from t1;

488

i k

489

3 3

490

alter table t1 add primary key (i);

491

insert into t1 values (3, 4) on duplicate key update k= k + 10;

492

ERROR 42S22: Unknown column 'at' in 'NEW'

493

select * from t1;

494

i k

495

3 13

496

replace into t1 values (3, 3);

497

ERROR 42S22: Unknown column 'at' in 'OLD'

498

select * from t1;

499

i k

500

drop table t1, t2;

501

create table t1 (i int, bt int, k int, key(k)) engine=myisam;

502

create table t2 (i int);

503

insert into t1 values (1, 1, 1), (2, 2, 2);

504

insert into t2 values (1), (2), (3);

505

create trigger bi before insert on t1 for each row set @a:= new.bt;

506

create trigger bu before update on t1 for each row set @a:= new.bt;

507

create trigger bd before delete on t1 for each row set @a:= old.bt;

508

alter table t1 drop column bt;

509

insert into t1 values (3, 3);

510

ERROR 42S22: Unknown column 'bt' in 'NEW'

511

select * from t1;

512

i k

513

1 1

514

2 2

515

update t1 set i = 2;

516

ERROR 42S22: Unknown column 'bt' in 'NEW'

517

select * from t1;

518

i k

519

1 1

520

2 2

521

delete from t1;

522

ERROR 42S22: Unknown column 'bt' in 'OLD'

523

select * from t1;

524

i k

525

1 1

526

2 2

527

load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k);

528

ERROR 42S22: Unknown column 'bt' in 'NEW'

529

select * from t1;

530

i k

531

1 1

532

2 2

533

insert into t1 select 3, 3;

534

ERROR 42S22: Unknown column 'bt' in 'NEW'

535

select * from t1;

536

i k

537

1 1

538

2 2

539

update t1, t2 set k = k + 10 where t1.i = t2.i;

540

ERROR 42S22: Unknown column 'bt' in 'NEW'

541

select * from t1;

542

i k

543

1 1

544

2 2

545

update t1, t2 set k = k + 10 where t1.i = t2.i and k < 2;

546

ERROR 42S22: Unknown column 'bt' in 'NEW'

547

select * from t1;

548

i k

549

1 1

550

2 2

551

delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;

552

ERROR 42S22: Unknown column 'bt' in 'OLD'

553

select * from t1;

554

i k

555

1 1

556

2 2

557

delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;

558

ERROR 42S22: Unknown column 'bt' in 'OLD'

559

select * from t1;

560

i k

561

1 1

562

2 2

563

alter table t1 add primary key (i);

564

drop trigger bi;

565

insert into t1 values (2, 4) on duplicate key update k= k + 10;

566

ERROR 42S22: Unknown column 'bt' in 'NEW'

567

select * from t1;

568

i k

569

1 1

570

2 2

571

replace into t1 values (2, 4);

572

ERROR 42S22: Unknown column 'bt' in 'OLD'

573

select * from t1;

574

i k

575

1 1

576

2 2

577

drop table t1, t2;

578

drop function if exists bug5893;

579

create table t1 (col1 int, col2 int);

580

insert into t1 values (1, 2);

581

create function bug5893 () returns int return 5;

582

create trigger t1_bu before update on t1 for each row set new.col1= bug5893();

583

drop function bug5893;

584

update t1 set col2 = 4;

585

ERROR 42000: FUNCTION test.bug5893 does not exist

586

drop trigger t1_bu;

587

drop table t1;

588

set sql_mode='ansi';

589

create table t1 ("t1 column" int);

590

create trigger t1_bi before insert on t1 for each row set new."t1 column" = 5;

591

set sql_mode="";

592

insert into t1 values (0);

593

create trigger t1_af after insert on t1 for each row set @a=10;

594

insert into t1 values (0);

595

select * from t1;

596

t1 column

597

598

599

select @a;

600

601

602

show triggers;

603

Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation

604

t1_bi INSERT t1 set new."t1 column" = 5 BEFORE # REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI root@localhost latin1 latin1_swedish_ci latin1_swedish_ci

605

t1_af INSERT t1 set @a=10 AFTER # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci

606

drop table t1;

607

set sql_mode="traditional";

608

create table t1 (a date);

609

insert into t1 values ('2004-01-00');

610

ERROR 22007: Incorrect date value: '2004-01-00' for column 'a' at row 1

611

set sql_mode="";

612

create trigger t1_bi before insert on t1 for each row set new.a = '2004-01-00';

613

set sql_mode="traditional";

614

insert into t1 values ('2004-01-01');

615

select * from t1;

616

617

2004-01-00

618

set sql_mode=default;

619

show create table t1;

620

Table Create Table

621

t1 CREATE TABLE `t1` (

622

`a` date DEFAULT NULL

623

) ENGINE=MyISAM DEFAULT CHARSET=latin1

624

show triggers;

625

Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation

626

t1_bi INSERT t1 set new.a = '2004-01-00' BEFORE # root@localhost latin1 latin1_swedish_ci latin1_swedish_ci

627

drop table t1;

628

create table t1 (id int);

629

create trigger t1_ai after insert on t1 for each row reset query cache;

630

ERROR 0A000: RESET is not allowed in stored function or trigger

631

create trigger t1_ai after insert on t1 for each row reset master;

632

ERROR 0A000: RESET is not allowed in stored function or trigger

633

create trigger t1_ai after insert on t1 for each row reset slave;

634

ERROR 0A000: RESET is not allowed in stored function or trigger

635

create trigger t1_ai after insert on t1 for each row flush hosts;

636

ERROR 0A000: FLUSH is not allowed in stored function or trigger

637

create trigger t1_ai after insert on t1 for each row flush tables with read lock;

638

ERROR 0A000: FLUSH is not allowed in stored function or trigger

639

create trigger t1_ai after insert on t1 for each row flush logs;

640

ERROR 0A000: FLUSH is not allowed in stored function or trigger

641

create trigger t1_ai after insert on t1 for each row flush status;

642

ERROR 0A000: FLUSH is not allowed in stored function or trigger

643

create trigger t1_ai after insert on t1 for each row flush slave;

644

ERROR 0A000: FLUSH is not allowed in stored function or trigger

645

create trigger t1_ai after insert on t1 for each row flush master;

646

ERROR 0A000: FLUSH is not allowed in stored function or trigger

647

create trigger t1_ai after insert on t1 for each row flush des_key_file;

648

ERROR 0A000: FLUSH is not allowed in stored function or trigger

649

create trigger t1_ai after insert on t1 for each row flush user_resources;

650

ERROR 0A000: FLUSH is not allowed in stored function or trigger

651

create trigger t1_ai after insert on t1 for each row flush tables;

652

ERROR 0A000: FLUSH is not allowed in stored function or trigger

653

create trigger t1_ai after insert on t1 for each row flush privileges;

654

ERROR 0A000: FLUSH is not allowed in stored function or trigger

655

drop procedure if exists p1;

656

create trigger t1_ai after insert on t1 for each row call p1();

657

create procedure p1() flush tables;

658

insert into t1 values (0);

659

ERROR 0A000: FLUSH is not allowed in stored function or trigger

660

drop procedure p1;

661

create procedure p1() reset query cache;

662

insert into t1 values (0);

663

ERROR 0A000: RESET is not allowed in stored function or trigger

664

drop procedure p1;

665

create procedure p1() reset master;

666

insert into t1 values (0);

667

ERROR 0A000: RESET is not allowed in stored function or trigger

668

drop procedure p1;

669

create procedure p1() reset slave;

670

insert into t1 values (0);

671

ERROR 0A000: RESET is not allowed in stored function or trigger

672

drop procedure p1;

673

create procedure p1() flush hosts;

674

insert into t1 values (0);

675

ERROR 0A000: FLUSH is not allowed in stored function or trigger

676

drop procedure p1;

677

create procedure p1() flush privileges;

678

insert into t1 values (0);

679

ERROR 0A000: FLUSH is not allowed in stored function or trigger

680

drop procedure p1;

681

create procedure p1() flush tables with read lock;

682

insert into t1 values (0);

683

ERROR 0A000: FLUSH is not allowed in stored function or trigger

684

drop procedure p1;

685

create procedure p1() flush tables;

686

insert into t1 values (0);

687

ERROR 0A000: FLUSH is not allowed in stored function or trigger

688

drop procedure p1;

689

create procedure p1() flush logs;

690

insert into t1 values (0);

691

ERROR 0A000: FLUSH is not allowed in stored function or trigger

692

drop procedure p1;

693

create procedure p1() flush status;

694

insert into t1 values (0);

695

ERROR 0A000: FLUSH is not allowed in stored function or trigger

696

drop procedure p1;

697

create procedure p1() flush slave;

698

insert into t1 values (0);

699

ERROR 0A000: FLUSH is not allowed in stored function or trigger

700

drop procedure p1;

701

create procedure p1() flush master;

702

insert into t1 values (0);

703

ERROR 0A000: FLUSH is not allowed in stored function or trigger

704

drop procedure p1;

705

create procedure p1() flush des_key_file;

706

insert into t1 values (0);

707

ERROR 0A000: FLUSH is not allowed in stored function or trigger

708

drop procedure p1;

709

create procedure p1() flush user_resources;

710

insert into t1 values (0);

711

ERROR 0A000: FLUSH is not allowed in stored function or trigger

712

drop procedure p1;

713

drop table t1;

714

create table t1 (id int, data int, username varchar(16));

715

insert into t1 (id, data) values (1, 0);

716

create trigger t1_whoupdated before update on t1 for each row

717

begin

718

declare user varchar(32);

719

declare i int;

720

select user() into user;

721

set NEW.username = user;

722

select count(*) from ((select 1) union (select 2)) as d1 into i;

723

end|

724

update t1 set data = 1;

725

update t1 set data = 2;

726

drop table t1;

727

create table t1 (c1 int, c2 datetime);

728

create trigger tr1 before insert on t1 for each row

729

begin

730

set new.c2= '2004-04-01';

731

select 'hello';

732

end|

733

ERROR 0A000: Not allowed to return a result set from a trigger

734

insert into t1 (c1) values (1),(2),(3);

735

select * from t1;

736

c1 c2

737

1 NULL

738

2 NULL

739

3 NULL

740

drop procedure if exists bug11587;

741

create procedure bug11587(x char(16))

742

begin

743

select "hello";

744

select "hello again";

745

end|

746

create trigger tr1 before insert on t1 for each row

747

begin

748

call bug11587();

749

set new.c2= '2004-04-02';

750

end|

751

insert into t1 (c1) values (4),(5),(6);

752

ERROR 0A000: Not allowed to return a result set from a trigger

753

select * from t1;

754

c1 c2

755

1 NULL

756

2 NULL

757

3 NULL

758

drop procedure bug11587;

759

drop table t1;

760

create table t1 (f1 integer);

761

create table t2 (f2 integer);

762

create trigger t1_ai after insert on t1

763

for each row insert into t2 values (new.f1+1);

764

create trigger t2_ai after insert on t2

765

for each row insert into t1 values (new.f2+1);

766

set @SAVE_SP_RECURSION_LEVELS=@@max_sp_recursion_depth;

767

set @@max_sp_recursion_depth=100;

768

insert into t1 values (1);

769

ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

770

set @@max_sp_recursion_depth=@SAVE_SP_RECURSION_LEVELS;

771

select * from t1;

772

773

774

select * from t2;

775

776

777

drop trigger t1_ai;

778

drop trigger t2_ai;

779

create trigger t1_bu before update on t1

780

for each row insert into t1 values (2);

781

update t1 set f1= 10;

782

ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

783

select * from t1;

784

785

786

drop trigger t1_bu;

787

create trigger t1_bu before update on t1

788

for each row delete from t1 where f1=new.f1;

789

update t1 set f1= 10;

790

ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

791

select * from t1;

792

793

794

drop trigger t1_bu;

795

create trigger t1_bi before insert on t1

796

for each row set new.f1=(select sum(f1) from t1);

797

insert into t1 values (3);

798

select * from t1;

799

800

801

802

drop trigger t1_bi;

803

drop tables t1, t2;

804

create table t1 (id int);

805

create table t2 (id int);

806

create trigger t1_bi before insert on t1 for each row insert into t2 values (new.id);

807

prepare stmt1 from "insert into t1 values (10)";

808

create procedure p1() insert into t1 values (10);

809

call p1();

810

drop trigger t1_bi;

811

execute stmt1;

812

call p1();

813

deallocate prepare stmt1;

814

drop procedure p1;

815

create table t3 (id int);

816

create trigger t1_bi after insert on t1 for each row insert into t2 values (new.id);

817

prepare stmt1 from "insert into t1 values (10)";

818

create procedure p1() insert into t1 values (10);

819

call p1();

820

drop trigger t1_bi;

821

create trigger t1_bi after insert on t1 for each row insert into t3 values (new.id);

822

execute stmt1;

823

call p1();

824

ERROR 42S02: Table 'test.t3' doesn't exist

825

deallocate prepare stmt1;

826

drop procedure p1;

827

drop table t1, t2, t3;

828

create table t1 (a int);

829

CREATE PROCEDURE `p1`()

830

begin

831

insert into t1 values (1);

832

end//

833

create trigger trg before insert on t1 for each row

834

begin

835

declare done int default 0;

836

set done= not done;

837

end//

838

CALL p1();

839

drop procedure p1;

840

drop table t1;

841

create trigger t1_bi before insert on test.t1 for each row set @a:=0;

842

ERROR 3D000: No database selected

843

create trigger test.t1_bi before insert on t1 for each row set @a:=0;

844

ERROR 42S02: Table 'test.t1' doesn't exist

845

drop trigger t1_bi;

846

ERROR 3D000: No database selected

847

create table t1 (id int);

848

create trigger t1_bi before insert on t1 for each row set @a:=new.id;

849

create trigger t1_ai after insert on test.t1 for each row set @b:=new.id;

850

insert into t1 values (101);

851

select @a, @b;

852

@a @b

853

101 101

854

select trigger_schema, trigger_name, event_object_schema,

855

event_object_table, action_statement from information_schema.triggers

856

where event_object_schema = 'test';

857

trigger_schema trigger_name event_object_schema event_object_table action_statement

858

test t1_bi test t1 set @a:=new.id

859

test t1_ai test t1 set @b:=new.id

860

rename table t1 to t2;

861

insert into t2 values (102);

862

select @a, @b;

863

@a @b

864

102 102

865

select trigger_schema, trigger_name, event_object_schema,

866

event_object_table, action_statement from information_schema.triggers

867

where event_object_schema = 'test';

868

trigger_schema trigger_name event_object_schema event_object_table action_statement

869

test t1_bi test t2 set @a:=new.id

870

test t1_ai test t2 set @b:=new.id

871

alter table t2 rename to t3;

872

insert into t3 values (103);

873

select @a, @b;

874

@a @b

875

103 103

876

select trigger_schema, trigger_name, event_object_schema,

877

event_object_table, action_statement from information_schema.triggers

878

where event_object_schema = 'test';

879

trigger_schema trigger_name event_object_schema event_object_table action_statement

880

test t1_bi test t3 set @a:=new.id

881

test t1_ai test t3 set @b:=new.id

882

alter table t3 rename to t4, add column val int default 0;

883

insert into t4 values (104, 1);

884

select @a, @b;

885

@a @b

886

104 104

887

select trigger_schema, trigger_name, event_object_schema,

888

event_object_table, action_statement from information_schema.triggers

889

where event_object_schema = 'test';

890

trigger_schema trigger_name event_object_schema event_object_table action_statement

891

test t1_bi test t4 set @a:=new.id

892

test t1_ai test t4 set @b:=new.id

893

drop trigger t1_bi;

894

drop trigger t1_ai;

895

drop table t4;

896

create database mysqltest;

897

use mysqltest;

898

create table t1 (id int);

899

create trigger t1_bi before insert on t1 for each row set @a:=new.id;

900

insert into t1 values (101);

901

select @a;

902

903

101

904

select trigger_schema, trigger_name, event_object_schema,

905

event_object_table, action_statement from information_schema.triggers

906

where event_object_schema = 'test' or event_object_schema = 'mysqltest';

907

trigger_schema trigger_name event_object_schema event_object_table action_statement

908

mysqltest t1_bi mysqltest t1 set @a:=new.id

909

rename table t1 to test.t2;

910

ERROR HY000: Trigger in wrong schema

911

insert into t1 values (102);

912

select @a;

913

914

102

915

select trigger_schema, trigger_name, event_object_schema,

916

event_object_table, action_statement from information_schema.triggers

917

where event_object_schema = 'test' or event_object_schema = 'mysqltest';

918

trigger_schema trigger_name event_object_schema event_object_table action_statement

919

mysqltest t1_bi mysqltest t1 set @a:=new.id

920

drop trigger test.t1_bi;

921

ERROR HY000: Trigger does not exist

922

alter table t1 rename to test.t1;

923

ERROR HY000: Trigger in wrong schema

924

insert into t1 values (103);

925

select @a;

926

927

103

928

select trigger_schema, trigger_name, event_object_schema,

929

event_object_table, action_statement from information_schema.triggers

930

where event_object_schema = 'test' or event_object_schema = 'mysqltest';

931

trigger_schema trigger_name event_object_schema event_object_table action_statement

932

mysqltest t1_bi mysqltest t1 set @a:=new.id

933

drop trigger test.t1_bi;

934

ERROR HY000: Trigger does not exist

935

alter table t1 rename to test.t1, add column val int default 0;

936

ERROR HY000: Trigger in wrong schema

937

insert into t1 values (104);

938

select @a;

939

940

104

941

select trigger_schema, trigger_name, event_object_schema,

942

event_object_table, action_statement from information_schema.triggers

943

where event_object_schema = 'test' or event_object_schema = 'mysqltest';

944

trigger_schema trigger_name event_object_schema event_object_table action_statement

945

mysqltest t1_bi mysqltest t1 set @a:=new.id

946

show create table t1;

947

Table Create Table

948

t1 CREATE TABLE `t1` (

949

`id` int(11) DEFAULT NULL

950

) ENGINE=MyISAM DEFAULT CHARSET=latin1

951

drop trigger test.t1_bi;

952

ERROR HY000: Trigger does not exist

953

drop trigger t1_bi;

954

drop table t1;

955

drop database mysqltest;

956

use test;

957

create table t1 (id int);

958

create trigger t1_bi before insert on t1 for each row set @a:=new.id;

959

create trigger t1_ai after insert on t1 for each row set @b:=new.id;

960

insert into t1 values (101);

961

select @a, @b;

962

@a @b

963

101 101

964

select trigger_schema, trigger_name, event_object_schema,

965

event_object_table, action_statement from information_schema.triggers

966

where event_object_schema = 'test';

967

trigger_schema trigger_name event_object_schema event_object_table action_statement

968

test t1_bi test t1 set @a:=new.id

969

test t1_ai test t1 set @b:=new.id

970

rename table t1 to t2;

971

ERROR HY000: Can't create/write to file './test/t1_ai.TRN~' (Errcode: 13)

972

insert into t1 values (102);

973

select @a, @b;

974

@a @b

975

102 102

976

select trigger_schema, trigger_name, event_object_schema,

977

event_object_table, action_statement from information_schema.triggers

978

where event_object_schema = 'test';

979

trigger_schema trigger_name event_object_schema event_object_table action_statement

980

test t1_bi test t1 set @a:=new.id

981

test t1_ai test t1 set @b:=new.id

982

drop trigger t1_bi;

983

drop trigger t1_ai;

984

drop table t1;

985

create table t1 (i int);

986

create trigger t1_bi before insert on t1 for each row return 0;

987

ERROR 42000: RETURN is only allowed in a FUNCTION

988

insert into t1 values (1);

989

drop table t1;

990

create table t1 (a varchar(64), b int);

991

create table t2 like t1;

992

create trigger t1_ai after insert on t1 for each row

993

set @a:= (select max(a) from t1);

994

insert into t1 (a) values

995

("Twas"),("brillig"),("and"),("the"),("slithy"),("toves"),

996

("Did"),("gyre"),("and"),("gimble"),("in"),("the"),("wabe");

997

create trigger t2_ai after insert on t2 for each row

998

set @a:= (select max(a) from t2);

999

insert into t2 select * from t1;

1000

load data infile '../../std_data/words.dat' into table t1 (a);

1001

drop trigger t1_ai;

1002

drop trigger t2_ai;

1003

create function f1() returns int return (select max(b) from t1);

1004

insert into t1 values

1005

("All",f1()),("mimsy",f1()),("were",f1()),("the",f1()),("borogoves",f1()),

1006

("And",f1()),("the",f1()),("mome", f1()),("raths",f1()),("outgrabe",f1());

1007

create function f2() returns int return (select max(b) from t2);

1008

insert into t2 select a, f2() from t1;

1009

load data infile '../../std_data/words.dat' into table t1 (a) set b:= f1();

1010

drop function f1;

1011

drop function f2;

1012

drop table t1, t2;

1013

create table t1(i int not null, j int not null, n numeric(15,2), primary key(i,j));

1014

create table t2(i int not null, n numeric(15,2), primary key(i));

1015

create trigger t1_ai after insert on t1 for each row

1016

begin

1017

declare sn numeric(15,2);

1018

select sum(n) into sn from t1 where i=new.i;

1019

replace into t2 values(new.i, sn);

1020

end|

1021

insert into t1 values

1022

(1,1,10.00),(1,2,10.00),(1,3,10.00),(1,4,10.00),(1,5,10.00),

1023

(1,6,10.00),(1,7,10.00),(1,8,10.00),(1,9,10.00),(1,10,10.00),

1024

(1,11,10.00),(1,12,10.00),(1,13,10.00),(1,14,10.00),(1,15,10.00);

1025

select * from t1;

1026

i j n

1027

1 1 10.00

1028

1 2 10.00

1029

1 3 10.00

1030

1 4 10.00

1031

1 5 10.00

1032

1 6 10.00

1033

1 7 10.00

1034

1 8 10.00

1035

1 9 10.00

1036

1 10 10.00

1037

1 11 10.00

1038

1 12 10.00

1039

1 13 10.00

1040

1 14 10.00

1041

1 15 10.00

1042

select * from t2;

1043

i n

1044

1 150.00

1045

drop tables t1, t2;

1046

DROP TABLE IF EXISTS t1;

1047

CREATE TABLE t1 (

1048

conn_id INT,

1049

trigger_conn_id INT

1050

);

1051

CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW

1052

SET NEW.trigger_conn_id = CONNECTION_ID();

1053

INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1);

1054

INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1);

1055

SELECT * FROM t1 WHERE conn_id != trigger_conn_id;

1056

conn_id trigger_conn_id

1057

DROP TRIGGER t1_bi;

1058

DROP TABLE t1;

1059

DROP TABLE IF EXISTS t1;

1060

CREATE TABLE t1 (i1 INT);

1061

SET @save_sql_mode=@@sql_mode;

1062

SET SQL_MODE='';

1063

CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW

1064

SET @x = 5/0;

1065

SET SQL_MODE='traditional';

1066

CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW

1067

SET @x = 5/0;

1068

SET @x=1;

1069

INSERT INTO t1 VALUES (@x);

1070

SELECT @x;

1071

1072

NULL

1073

SET @x=2;

1074

UPDATE t1 SET i1 = @x;

1075

Warnings:

1076

Error 1365 Division by 0

1077

SELECT @x;

1078

1079

NULL

1080

SET SQL_MODE='';

1081

SET @x=3;

1082

INSERT INTO t1 VALUES (@x);

1083

SELECT @x;

1084

1085

NULL

1086

SET @x=4;

1087

UPDATE t1 SET i1 = @x;

1088

Warnings:

1089

Error 1365 Division by 0

1090

Error 1365 Division by 0

1091

SELECT @x;

1092

1093

NULL

1094

SET @@sql_mode=@save_sql_mode;

1095

DROP TRIGGER t1_ai;

1096

DROP TRIGGER t1_au;

1097

DROP TABLE t1;

1098

DROP TABLE IF EXISTS t1;

1099

DROP PROCEDURE IF EXISTS p1;

1100

DROP PROCEDURE IF EXISTS p2;

1101

CREATE TABLE t1 (i1 INT);

1102

INSERT INTO t1 VALUES (3);

1103

CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET i1 = 5;

1104

CREATE PROCEDURE p2(INOUT i1 INT) DETERMINISTIC NO SQL SET i1 = i1 * 7;

1105

CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW

1106

BEGIN

1107

CALL p1(NEW.i1);

1108

CALL p2(NEW.i1);

1109

END//

1110

UPDATE t1 SET i1 = 11 WHERE i1 = 3;

1111

DROP TRIGGER t1_bu;

1112

DROP PROCEDURE p2;

1113

DROP PROCEDURE p1;

1114

INSERT INTO t1 VALUES (13);

1115

CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 17;

1116

CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW

1117

CALL p1(OLD.i1);

1118

UPDATE t1 SET i1 = 19 WHERE i1 = 13;

1119

ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger

1120

DROP TRIGGER t1_bu;

1121

DROP PROCEDURE p1;

1122

INSERT INTO t1 VALUES (23);

1123

CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 29;

1124

CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW

1125

CALL p1(OLD.i1);

1126

UPDATE t1 SET i1 = 31 WHERE i1 = 23;

1127

ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger

1128

DROP TRIGGER t1_bu;

1129

DROP PROCEDURE p1;

1130

INSERT INTO t1 VALUES (37);

1131

CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 41;

1132

CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW

1133

CALL p1(NEW.i1);

1134

UPDATE t1 SET i1 = 43 WHERE i1 = 37;

1135

ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger

1136

DROP TRIGGER t1_au;

1137

DROP PROCEDURE p1;

1138

INSERT INTO t1 VALUES (47);

1139

CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 49;

1140

CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW

1141

CALL p1(NEW.i1);

1142

UPDATE t1 SET i1 = 51 WHERE i1 = 47;

1143

ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger

1144

DROP TRIGGER t1_au;

1145

DROP PROCEDURE p1;

1146

SELECT * FROM t1;

1147

1148

1149

1150

1151

1152

1153

DROP TABLE t1;

1154

create trigger wont_work after update on mysql.user for each row

1155

begin

1156

set @a:= 1;

1157

end|

1158

ERROR HY000: Triggers can not be created on system tables

1159

use mysql|

1160

create trigger wont_work after update on event for each row

1161

begin

1162

set @a:= 1;

1163

end|

1164

ERROR HY000: Triggers can not be created on system tables

1165

use test|

1166

DROP TABLE IF EXISTS t1;

1167

DROP TABLE IF EXISTS t2;

1168

CREATE TABLE t1(c INT);

1169

CREATE TABLE t2(c INT);

1170

CREATE DEFINER=1234567890abcdefGHIKL@localhost

1171

TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1;

1172

ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)

1173

CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY

1174

TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW SET @a = 2;

1175

ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)

1176

DROP TABLE t1;

1177

DROP TABLE t2;

1178

drop table if exists t1;

1179

drop table if exists t2;

1180

drop table if exists t3;

1181

drop table if exists t4;

1182

SET @save_sql_mode=@@sql_mode;

1183

SET sql_mode='TRADITIONAL'|

1184

create table t1 (id int(10) not null primary key, v int(10) )|

1185

create table t2 (id int(10) not null primary key, v int(10) )|

1186

create table t3 (id int(10) not null primary key, v int(10) )|

1187

create table t4 (c int)|

1188

create trigger t4_bi before insert on t4 for each row set @t4_bi_called:=1|

1189

create trigger t4_bu before update on t4 for each row set @t4_bu_called:=1|

1190

insert into t1 values(10, 10)|

1191

set @a:=1/0|

1192

Warnings:

1193

Error 1365 Division by 0

1194

select 1/0 from t1|

1195

1/0

1196

NULL

1197

Warnings:

1198

Error 1365 Division by 0

1199

create trigger t1_bi before insert on t1 for each row set @a:=1/0|

1200

insert into t1 values(20, 20)|

1201

Warnings:

1202

Error 1365 Division by 0

1203

drop trigger t1_bi|

1204

create trigger t1_bi before insert on t1 for each row

1205

begin

1206

insert into t2 values (new.id, new.v);

1207

update t2 set v=v+1 where id= new.id;

1208

replace t3 values (new.id, 0);

1209

update t2, t3 set t2.v=new.v, t3.v=new.v where t2.id=t3.id;

1210

create temporary table t5 select * from t1;

1211

delete from t5;

1212

insert into t5 select * from t1;

1213

insert into t4 values (0);

1214

set @check= (select count(*) from t5);

1215

update t4 set c= @check;

1216

drop temporary table t5;

1217

set @a:=1/0;

1218

end|

1219

set @check=0, @t4_bi_called=0, @t4_bu_called=0|

1220

insert into t1 values(30, 30)|

1221

Warnings:

1222

Error 1365 Division by 0

1223

select @check, @t4_bi_called, @t4_bu_called|

1224

@check @t4_bi_called @t4_bu_called

1225

2 1 1

1226

SET @@sql_mode=@save_sql_mode;

1227

drop table t1;

1228

drop table t2;

1229

drop table t3;

1230

drop table t4;

1231

drop table if exists t1;

1232

create table t1 (i int, j int key);

1233

insert into t1 values (1,1), (2,2), (3,3);

1234

create trigger t1_bu before update on t1 for each row

1235

set new.j = new.j + 10;

1236

update t1 set i= i+ 10 where j > 2;

1237

select * from t1;

1238

i j

1239

1 1

1240

2 2

1241

13 13

1242

drop table t1;

1243

CREATE TABLE t1 (a INT PRIMARY KEY);

1244

CREATE TABLE t2 (a INT PRIMARY KEY);

1245

INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);

1246

CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW

1247

INSERT INTO t2 VALUES (OLD.a);

1248

FLUSH STATUS;

1249

TRUNCATE t1;

1250

SHOW STATUS LIKE 'handler_delete';

1251

Variable_name Value

1252

Handler_delete 0

1253

SELECT COUNT(*) FROM t2;

1254

COUNT(*)

1255

1256

INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);

1257

DELETE FROM t2;

1258

FLUSH STATUS;

1259

DELETE FROM t1;

1260

SHOW STATUS LIKE 'handler_delete';

1261

Variable_name Value

1262

Handler_delete 8

1263

SELECT COUNT(*) FROM t2;

1264

COUNT(*)

1265

1266

DROP TRIGGER trg_t1;

1267

DROP TABLE t1,t2;

1268

drop table if exists t1;

1269

drop function if exists f1;

1270

create table t1 (i int);

1271

create function f1() returns int return 10;

1272

create trigger t1_bi before insert on t1 for each row set @a:= f1() + 10;

1273

insert into t1 values ();

1274

select @a;

1275

1276

1277

insert into t1 values ();

1278

select @a;

1279

1280

1281

drop table t1;

1282

drop function f1;

1283

drop table if exists t1;

1284

create table t1(a int, b varchar(50));

1285

drop trigger not_a_trigger;

1286

ERROR HY000: Trigger does not exist

1287

drop trigger if exists not_a_trigger;

1288

Warnings:

1289

Note 1360 Trigger does not exist

1290

create trigger t1_bi before insert on t1

1291

for each row set NEW.b := "In trigger t1_bi";

1292

insert into t1 values (1, "a");

1293

drop trigger if exists t1_bi;

1294

insert into t1 values (2, "b");

1295

drop trigger if exists t1_bi;

1296

Warnings:

1297

Note 1360 Trigger does not exist

1298

insert into t1 values (3, "c");

1299

select * from t1;

1300

a b

1301

1 In trigger t1_bi

1302

2 b

1303

3 c

1304

drop table t1;

1305

CREATE TABLE t1 (

1306

id int NOT NULL DEFAULT '0',

1307

a varchar(10) NOT NULL,

1308

b varchar(10),

1309

c varchar(10),

1310

d timestamp NOT NULL,

1311

PRIMARY KEY (id, a)

1312

);

1313

CREATE TABLE t2 (

1314

fubar_id int unsigned NOT NULL DEFAULT '0',

1315

last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

1316

PRIMARY KEY (fubar_id)

1317

);

1318

CREATE TRIGGER fubar_change

1319

AFTER UPDATE ON t1

1320

FOR EACH ROW

1321

BEGIN

1322

INSERT INTO t2 (fubar_id, last_change_time)

1323

SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time

1324

FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c)

1325

ON DUPLICATE KEY UPDATE

1326

last_change_time =

1327

IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time);

1328

END

1329

1330

INSERT INTO t1 (id,a, b,c,d) VALUES

1331

(1,'a','b','c',now()),(2,'a','b','c',now());

1332

UPDATE t1 SET c='Bang!' WHERE id=1;

1333

SELECT fubar_id FROM t2;

1334

fubar_id

1335

1336

DROP TABLE t1,t2;

1337

DROP TABLE IF EXISTS bug21825_A;

1338

DROP TABLE IF EXISTS bug21825_B;

1339

CREATE TABLE bug21825_A (id int(10));

1340

CREATE TABLE bug21825_B (id int(10));

1341

CREATE TRIGGER trgA AFTER INSERT ON bug21825_A

1342

FOR EACH ROW

1343

BEGIN

1344

INSERT INTO bug21825_B (id) values (1);

1345

END//

1346

INSERT INTO bug21825_A (id) VALUES (10);

1347

INSERT INTO bug21825_A (id) VALUES (20);

1348

DROP TABLE bug21825_B;

1349

DELETE FROM bug21825_A WHERE id = 20;

1350

DROP TABLE bug21825_A;

1351

DROP TABLE IF EXISTS bug22580_t1;

1352

DROP PROCEDURE IF EXISTS bug22580_proc_1;

1353

DROP PROCEDURE IF EXISTS bug22580_proc_2;

1354

CREATE TABLE bug22580_t1 (a INT, b INT);

1355

CREATE PROCEDURE bug22580_proc_2()

1356

BEGIN

1357

DROP TABLE IF EXISTS bug22580_tmp;

1358

CREATE TEMPORARY TABLE bug22580_tmp (a INT);

1359

DROP TABLE bug22580_tmp;

1360

END||

1361

CREATE PROCEDURE bug22580_proc_1()

1362

BEGIN

1363

CALL bug22580_proc_2();

1364

END||

1365

CREATE TRIGGER t1bu BEFORE UPDATE ON bug22580_t1

1366

FOR EACH ROW

1367

BEGIN

1368

CALL bug22580_proc_1();

1369

END||

1370

INSERT INTO bug22580_t1 VALUES (1,1);

1371

DROP TABLE bug22580_t1;

1372

DROP PROCEDURE bug22580_proc_1;

1373

DROP PROCEDURE bug22580_proc_2;

1374

DROP TRIGGER IF EXISTS trg27006_a_update;

1375

DROP TRIGGER IF EXISTS trg27006_a_insert;

1376

CREATE TABLE t1 (

1377

`id` int(10) unsigned NOT NULL auto_increment,

1378

`val` varchar(10) NOT NULL,

1379

PRIMARY KEY (`id`)

1380

);

1381

CREATE TABLE t2 like t1;

1382

CREATE TRIGGER trg27006_a_insert AFTER INSERT ON t1 FOR EACH ROW

1383

BEGIN

1384

insert into t2 values (NULL,new.val);

1385

END |

1386

CREATE TRIGGER trg27006_a_update AFTER UPDATE ON t1 FOR EACH ROW

1387

BEGIN

1388

insert into t2 values (NULL,new.val);

1389

END |

1390

INSERT INTO t1(val) VALUES ('test1'),('test2');

1391

SELECT * FROM t1;

1392

id val

1393

1 test1

1394

2 test2

1395

SELECT * FROM t2;

1396

id val

1397

1 test1

1398

2 test2

1399

INSERT INTO t1 VALUES (2,'test2') ON DUPLICATE KEY UPDATE val=VALUES(val);

1400

INSERT INTO t1 VALUES (2,'test3') ON DUPLICATE KEY UPDATE val=VALUES(val);

1401

INSERT INTO t1 VALUES (3,'test4') ON DUPLICATE KEY UPDATE val=VALUES(val);

1402

SELECT * FROM t1;

1403

id val

1404

1 test1

1405

2 test3

1406

3 test4

1407

SELECT * FROM t2;

1408

id val

1409

1 test1

1410

2 test2

1411

3 test3

1412

4 test4

1413

DROP TRIGGER trg27006_a_insert;

1414

DROP TRIGGER trg27006_a_update;

1415

drop table t1,t2;

1416

drop table if exists t1, t2, t3;

1417

create table t1 (i int);

1418

create trigger t1_bi before insert on t1 for each row set new.i = 7;

1419

create trigger t1_ai after insert on t1 for each row set @a := 7;

1420

create table t2 (j int);

1421

insert into t2 values (1), (2);

1422

set @a:="";

1423

create table if not exists t1 select * from t2;

1424

Warnings:

1425

Note 1050 Table 't1' already exists

1426

select * from t1;

1427

1428

1429

1430

select @a;

1431

1432

1433

drop trigger t1_bi;

1434

drop trigger t1_ai;

1435

create table t3 (isave int);

1436

create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i);

1437

create table if not exists t1 select * from t2;

1438

Warnings:

1439

Note 1050 Table 't1' already exists

1440

select * from t1;

1441

1442

1443

1444

1445

1446

select * from t3;

1447

isave

1448

1449

1450

drop table t1, t2, t3;

1451

CREATE TABLE t1 (id INTEGER);

1452

CREATE TABLE t2 (id INTEGER);

1453

INSERT INTO t2 VALUES (1),(2);

1454

CREATE TRIGGER t1_test AFTER INSERT ON t1 FOR EACH ROW

1455

INSERT INTO t2 VALUES (new.id);

1456

SELECT GET_LOCK('B26162',120);

1457

GET_LOCK('B26162',120)

1458

1459

SELECT 'rl_acquirer', GET_LOCK('B26162',120), id FROM t2 WHERE id = 1;

1460

SET SESSION LOW_PRIORITY_UPDATES=1;

1461

SET GLOBAL LOW_PRIORITY_UPDATES=1;

1462

INSERT INTO t1 VALUES (5);

1463

SELECT 'rl_contender', id FROM t2 WHERE id > 1;

1464

SELECT RELEASE_LOCK('B26162');

1465

RELEASE_LOCK('B26162')

1466

1467

rl_acquirer GET_LOCK('B26162',120) id

1468

rl_acquirer 1 1

1469

SELECT RELEASE_LOCK('B26162');

1470

RELEASE_LOCK('B26162')

1471

1472

rl_contender id

1473

rl_contender 2

1474

DROP TRIGGER t1_test;

1475

DROP TABLE t1,t2;

1476

SET SESSION LOW_PRIORITY_UPDATES=DEFAULT;

1477

SET GLOBAL LOW_PRIORITY_UPDATES=DEFAULT;

1478

1479

Bug#28502 Triggers that update another innodb table will block

1480

on X lock unnecessarily

1481

1482

Ensure we do not open and lock tables for triggers we do not fire.

1483

1484

drop table if exists t1, t2;

1485

drop trigger if exists trg_bug28502_au;

1486

create table t1 (id int, count int);

1487

create table t2 (id int);

1488

create trigger trg_bug28502_au before update on t2

1489

for each row

1490

begin

1491

if (new.id is not null) then

1492

update t1 set count= count + 1 where id = old.id;

1493

end if;

1494

end|

1495

insert into t1 (id, count) values (1, 0);

1496

lock table t1 write;

1497

insert into t2 set id=1;

1498

unlock tables;

1499

update t2 set id=1 where id=1;

1500

select * from t1;

1501

id count

1502

1 1

1503

select * from t2;

1504

1505

1506

drop table t1, t2;

1507

1508

Additionally, provide test coverage for triggers and

1509

all MySQL data changing commands.

1510

1511

drop table if exists t1, t2, t1_op_log;

1512

drop view if exists v1;

1513

drop trigger if exists trg_bug28502_bi;

1514

drop trigger if exists trg_bug28502_ai;

1515

drop trigger if exists trg_bug28502_bu;

1516

drop trigger if exists trg_bug28502_au;

1517

drop trigger if exists trg_bug28502_bd;

1518

drop trigger if exists trg_bug28502_ad;

1519

create table t1 (id int primary key auto_increment, operation varchar(255));

1520

create table t2 (id int primary key);

1521

create table t1_op_log(operation varchar(255));

1522

create view v1 as select * from t1;

1523

create trigger trg_bug28502_bi before insert on t1

1524

for each row

1525

insert into t1_op_log (operation)

1526

values (concat("Before INSERT, new=", new.operation));

1527

create trigger trg_bug28502_ai after insert on t1

1528

for each row

1529

insert into t1_op_log (operation)

1530

values (concat("After INSERT, new=", new.operation));

1531

create trigger trg_bug28502_bu before update on t1

1532

for each row

1533

insert into t1_op_log (operation)

1534

values (concat("Before UPDATE, new=", new.operation,

1535

", old=", old.operation));

1536

create trigger trg_bug28502_au after update on t1

1537

for each row

1538

insert into t1_op_log (operation)

1539

values (concat("After UPDATE, new=", new.operation,

1540

", old=", old.operation));

1541

create trigger trg_bug28502_bd before delete on t1

1542

for each row

1543

insert into t1_op_log (operation)

1544

values (concat("Before DELETE, old=", old.operation));

1545

create trigger trg_bug28502_ad after delete on t1

1546

for each row

1547

insert into t1_op_log (operation)

1548

values (concat("After DELETE, old=", old.operation));

1549

insert into t1 (operation) values ("INSERT");

1550

set @id=last_insert_id();

1551

select * from t1;

1552

id operation

1553

1 INSERT

1554

select * from t1_op_log;

1555

operation

1556

Before INSERT, new=INSERT

1557

After INSERT, new=INSERT

1558

truncate t1_op_log;

1559

update t1 set operation="UPDATE" where id=@id;

1560

select * from t1;

1561

id operation

1562

1 UPDATE

1563

select * from t1_op_log;

1564

operation

1565

Before UPDATE, new=UPDATE, old=INSERT

1566

After UPDATE, new=UPDATE, old=INSERT

1567

truncate t1_op_log;

1568

delete from t1 where id=@id;

1569

select * from t1;

1570

id operation

1571

select * from t1_op_log;

1572

operation

1573

Before DELETE, old=UPDATE

1574

After DELETE, old=UPDATE

1575

truncate t1;

1576

truncate t1_op_log;

1577

insert into t1 (id, operation) values

1578

(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key")

1579

on duplicate key update id=NULL, operation="Should never happen";

1580

set @id=last_insert_id();

1581

select * from t1;

1582

id operation

1583

1 INSERT ON DUPLICATE KEY UPDATE, inserting a new key

1584

select * from t1_op_log;

1585

operation

1586

Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key

1587

After INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key

1588

truncate t1_op_log;

1589

insert into t1 (id, operation) values

1590

(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same")

1591

on duplicate key update id=NULL,

1592

operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate";

1593

select * from t1;

1594

id operation

1595

0 INSERT ON DUPLICATE KEY UPDATE, updating the duplicate

1596

select * from t1_op_log;

1597

operation

1598

Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, the key value is the same

1599

Before UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key

1600

After UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key

1601

truncate t1;

1602

truncate t1_op_log;

1603

replace into t1 values (NULL, "REPLACE, inserting a new key");

1604

set @id=last_insert_id();

1605

select * from t1;

1606

id operation

1607

1 REPLACE, inserting a new key

1608

select * from t1_op_log;

1609

operation

1610

Before INSERT, new=REPLACE, inserting a new key

1611

After INSERT, new=REPLACE, inserting a new key

1612

truncate t1_op_log;

1613

replace into t1 values (@id, "REPLACE, deleting the duplicate");

1614

select * from t1;

1615

id operation

1616

1 REPLACE, deleting the duplicate

1617

select * from t1_op_log;

1618

operation

1619

Before INSERT, new=REPLACE, deleting the duplicate

1620

Before DELETE, old=REPLACE, inserting a new key

1621

After DELETE, old=REPLACE, inserting a new key

1622

After INSERT, new=REPLACE, deleting the duplicate

1623

truncate t1;

1624

truncate t1_op_log;

1625

create table if not exists t1

1626

select NULL, "CREATE TABLE ... SELECT, inserting a new key";

1627

Warnings:

1628

Note 1050 Table 't1' already exists

1629

set @id=last_insert_id();

1630

select * from t1;

1631

id operation

1632

1 CREATE TABLE ... SELECT, inserting a new key

1633

select * from t1_op_log;

1634

operation

1635

Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key

1636

After INSERT, new=CREATE TABLE ... SELECT, inserting a new key

1637

truncate t1_op_log;

1638

create table if not exists t1 replace

1639

select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";

1640

Warnings:

1641

Note 1050 Table 't1' already exists

1642

select * from t1;

1643

id operation

1644

1 CREATE TABLE ... REPLACE SELECT, deleting a duplicate key

1645

select * from t1_op_log;

1646

operation

1647

Before INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key

1648

Before DELETE, old=CREATE TABLE ... SELECT, inserting a new key

1649

After DELETE, old=CREATE TABLE ... SELECT, inserting a new key

1650

After INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key

1651

truncate t1;

1652

truncate t1_op_log;

1653

insert into t1 (id, operation)

1654

select NULL, "INSERT ... SELECT, inserting a new key";

1655

set @id=last_insert_id();

1656

select * from t1;

1657

id operation

1658

1 INSERT ... SELECT, inserting a new key

1659

select * from t1_op_log;

1660

operation

1661

Before INSERT, new=INSERT ... SELECT, inserting a new key

1662

After INSERT, new=INSERT ... SELECT, inserting a new key

1663

truncate t1_op_log;

1664

insert into t1 (id, operation)

1665

select @id,

1666

"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"

1667

on duplicate key update id=NULL,

1668

operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate";

1669

select * from t1;

1670

id operation

1671

0 INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate

1672

select * from t1_op_log;

1673

operation

1674

Before INSERT, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate

1675

Before UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key

1676

After UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key

1677

truncate t1;

1678

truncate t1_op_log;

1679

replace into t1 (id, operation)

1680

select NULL, "REPLACE ... SELECT, inserting a new key";

1681

set @id=last_insert_id();

1682

select * from t1;

1683

id operation

1684

1 REPLACE ... SELECT, inserting a new key

1685

select * from t1_op_log;

1686

operation

1687

Before INSERT, new=REPLACE ... SELECT, inserting a new key

1688

After INSERT, new=REPLACE ... SELECT, inserting a new key

1689

truncate t1_op_log;

1690

replace into t1 (id, operation)

1691

select @id, "REPLACE ... SELECT, deleting a duplicate";

1692

select * from t1;

1693

id operation

1694

1 REPLACE ... SELECT, deleting a duplicate

1695

select * from t1_op_log;

1696

operation

1697

Before INSERT, new=REPLACE ... SELECT, deleting a duplicate

1698

Before DELETE, old=REPLACE ... SELECT, inserting a new key

1699

After DELETE, old=REPLACE ... SELECT, inserting a new key

1700

After INSERT, new=REPLACE ... SELECT, deleting a duplicate

1701

truncate t1;

1702

truncate t1_op_log;

1703

insert into t1 (id, operation) values (1, "INSERT for multi-DELETE");

1704

insert into t2 (id) values (1);

1705

delete t1.*, t2.* from t1, t2 where t1.id=1;

1706

select * from t1;

1707

id operation

1708

select * from t2;

1709

1710

select * from t1_op_log;

1711

operation

1712

Before INSERT, new=INSERT for multi-DELETE

1713

After INSERT, new=INSERT for multi-DELETE

1714

Before DELETE, old=INSERT for multi-DELETE

1715

After DELETE, old=INSERT for multi-DELETE

1716

truncate t1;

1717

truncate t2;

1718

truncate t1_op_log;

1719

insert into t1 (id, operation) values (1, "INSERT for multi-UPDATE");

1720

insert into t2 (id) values (1);

1721

update t1, t2 set t1.id=2, operation="multi-UPDATE" where t1.id=1;

1722

update t1, t2

1723

set t2.id=3, operation="multi-UPDATE, SET for t2, but the trigger is fired" where t1.id=2;

1724

select * from t1;

1725

id operation

1726

2 multi-UPDATE, SET for t2, but the trigger is fired

1727

select * from t2;

1728

1729

1730

select * from t1_op_log;

1731

operation

1732

Before INSERT, new=INSERT for multi-UPDATE

1733

After INSERT, new=INSERT for multi-UPDATE

1734

Before UPDATE, new=multi-UPDATE, old=INSERT for multi-UPDATE

1735

After UPDATE, new=multi-UPDATE, old=INSERT for multi-UPDATE

1736

Before UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE

1737

After UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE

1738

truncate table t1;

1739

truncate table t2;

1740

truncate table t1_op_log;

1741

1742

Now do the same but use a view instead of the base table.

1743

1744

insert into v1 (operation) values ("INSERT");

1745

set @id=last_insert_id();

1746

select * from t1;

1747

id operation

1748

1 INSERT

1749

select * from t1_op_log;

1750

operation

1751

Before INSERT, new=INSERT

1752

After INSERT, new=INSERT

1753

truncate t1_op_log;

1754

update v1 set operation="UPDATE" where id=@id;

1755

select * from t1;

1756

id operation

1757

1 UPDATE

1758

select * from t1_op_log;

1759

operation

1760

Before UPDATE, new=UPDATE, old=INSERT

1761

After UPDATE, new=UPDATE, old=INSERT

1762

truncate t1_op_log;

1763

delete from v1 where id=@id;

1764

select * from t1;

1765

id operation

1766

select * from t1_op_log;

1767

operation

1768

Before DELETE, old=UPDATE

1769

After DELETE, old=UPDATE

1770

truncate t1;

1771

truncate t1_op_log;

1772

insert into v1 (id, operation) values

1773

(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key")

1774

on duplicate key update id=NULL, operation="Should never happen";

1775

set @id=last_insert_id();

1776

select * from t1;

1777

id operation

1778

1 INSERT ON DUPLICATE KEY UPDATE, inserting a new key

1779

select * from t1_op_log;

1780

operation

1781

Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key

1782

After INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key

1783

truncate t1_op_log;

1784

insert into v1 (id, operation) values

1785

(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same")

1786

on duplicate key update id=NULL,

1787

operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate";

1788

select * from t1;

1789

id operation

1790

0 INSERT ON DUPLICATE KEY UPDATE, updating the duplicate

1791

select * from t1_op_log;

1792

operation

1793

Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, the key value is the same

1794

Before UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key

1795

After UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key

1796

truncate t1;

1797

truncate t1_op_log;

1798

replace into v1 values (NULL, "REPLACE, inserting a new key");

1799

set @id=last_insert_id();

1800

select * from t1;

1801

id operation

1802

1 REPLACE, inserting a new key

1803

select * from t1_op_log;

1804

operation

1805

Before INSERT, new=REPLACE, inserting a new key

1806

After INSERT, new=REPLACE, inserting a new key

1807

truncate t1_op_log;

1808

replace into v1 values (@id, "REPLACE, deleting the duplicate");

1809

select * from t1;

1810

id operation

1811

1 REPLACE, deleting the duplicate

1812

select * from t1_op_log;

1813

operation

1814

Before INSERT, new=REPLACE, deleting the duplicate

1815

Before DELETE, old=REPLACE, inserting a new key

1816

After DELETE, old=REPLACE, inserting a new key

1817

After INSERT, new=REPLACE, deleting the duplicate

1818

truncate t1;

1819

truncate t1_op_log;

1820

create table if not exists v1

1821

select NULL, "CREATE TABLE ... SELECT, inserting a new key";

1822

Warnings:

1823

Note 1050 Table 'v1' already exists

1824

set @id=last_insert_id();

1825

select * from t1;

1826

id operation

1827

1 CREATE TABLE ... SELECT, inserting a new key

1828

select * from t1_op_log;

1829

operation

1830

Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key

1831

After INSERT, new=CREATE TABLE ... SELECT, inserting a new key

1832

truncate t1_op_log;

1833

create table if not exists v1 replace

1834

select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";

1835

Warnings:

1836

Note 1050 Table 'v1' already exists

1837

select * from t1;

1838

id operation

1839

1 CREATE TABLE ... REPLACE SELECT, deleting a duplicate key

1840

select * from t1_op_log;

1841

operation

1842

Before INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key

1843

Before DELETE, old=CREATE TABLE ... SELECT, inserting a new key

1844

After DELETE, old=CREATE TABLE ... SELECT, inserting a new key

1845

After INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key

1846

truncate t1;

1847

truncate t1_op_log;

1848

insert into v1 (id, operation)

1849

select NULL, "INSERT ... SELECT, inserting a new key";

1850

set @id=last_insert_id();

1851

select * from t1;

1852

id operation

1853

1 INSERT ... SELECT, inserting a new key

1854

select * from t1_op_log;

1855

operation

1856

Before INSERT, new=INSERT ... SELECT, inserting a new key

1857

After INSERT, new=INSERT ... SELECT, inserting a new key

1858

truncate t1_op_log;

1859

insert into v1 (id, operation)

1860

select @id,

1861

"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"

1862

on duplicate key update id=NULL,

1863

operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate";

1864

select * from t1;

1865

id operation

1866

0 INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate

1867

select * from t1_op_log;

1868

operation

1869

Before INSERT, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate

1870

Before UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key

1871

After UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key

1872

truncate t1;

1873

truncate t1_op_log;

1874

replace into v1 (id, operation)

1875

select NULL, "REPLACE ... SELECT, inserting a new key";

1876

set @id=last_insert_id();

1877

select * from t1;

1878

id operation

1879

1 REPLACE ... SELECT, inserting a new key

1880

select * from t1_op_log;

1881

operation

1882

Before INSERT, new=REPLACE ... SELECT, inserting a new key

1883

After INSERT, new=REPLACE ... SELECT, inserting a new key

1884

truncate t1_op_log;

1885

replace into v1 (id, operation)

1886

select @id, "REPLACE ... SELECT, deleting a duplicate";

1887

select * from t1;

1888

id operation

1889

1 REPLACE ... SELECT, deleting a duplicate

1890

select * from t1_op_log;

1891

operation

1892

Before INSERT, new=REPLACE ... SELECT, deleting a duplicate

1893

Before DELETE, old=REPLACE ... SELECT, inserting a new key

1894

After DELETE, old=REPLACE ... SELECT, inserting a new key

1895

After INSERT, new=REPLACE ... SELECT, deleting a duplicate

1896

truncate t1;

1897

truncate t1_op_log;

1898

insert into v1 (id, operation) values (1, "INSERT for multi-DELETE");

1899

insert into t2 (id) values (1);

1900

delete v1.*, t2.* from v1, t2 where v1.id=1;

1901

select * from t1;

1902

id operation

1903

select * from t2;

1904

1905

select * from t1_op_log;

1906

operation

1907

Before INSERT, new=INSERT for multi-DELETE

1908

After INSERT, new=INSERT for multi-DELETE

1909

Before DELETE, old=INSERT for multi-DELETE

1910

After DELETE, old=INSERT for multi-DELETE

1911

truncate t1;

1912

truncate t2;

1913

truncate t1_op_log;

1914

insert into v1 (id, operation) values (1, "INSERT for multi-UPDATE");

1915

insert into t2 (id) values (1);

1916

update v1, t2 set v1.id=2, operation="multi-UPDATE" where v1.id=1;

1917

update v1, t2

1918

set t2.id=3, operation="multi-UPDATE, SET for t2, but the trigger is fired" where v1.id=2;

1919

select * from t1;

1920

id operation

1921

2 multi-UPDATE, SET for t2, but the trigger is fired

1922

select * from t2;

1923

1924

1925

select * from t1_op_log;

1926

operation

1927

Before INSERT, new=INSERT for multi-UPDATE

1928

After INSERT, new=INSERT for multi-UPDATE

1929

Before UPDATE, new=multi-UPDATE, old=INSERT for multi-UPDATE

1930

After UPDATE, new=multi-UPDATE, old=INSERT for multi-UPDATE

1931

Before UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE

1932

After UPDATE, new=multi-UPDATE, SET for t2, but the trigger is fired, old=multi-UPDATE

1933

drop view v1;

1934

drop table t1, t2, t1_op_log;

1935

1936

Bug#27248 Triggers: error if insert affects temporary table

1937

1938

The bug was fixed by the fix for Bug#26141

1939

1940

drop table if exists t1;

1941

drop temporary table if exists t2;

1942

create table t1 (s1 int);

1943

create temporary table t2 (s1 int);

1944

create trigger t1_bi before insert on t1 for each row insert into t2 values (0);

1945

create trigger t1_bd before delete on t1 for each row delete from t2;

1946

insert into t1 values (0);

1947

insert into t1 values (0);

1948

select * from t1;

1949

1950

1951

1952

select * from t2;

1953

1954

1955

1956

delete from t1;

1957

select * from t1;

1958

1959

select * from t2;

1960

1961

drop table t1;

1962

drop temporary table t2;

1963

#------------------------------------------------------------------------

1964

# Bug#39953 Triggers are not working properly with multi table updates

1965

#------------------------------------------------------------------------

1966

DROP TABLE IF EXISTS t1;

1967

DROP TRIGGER IF EXISTS t_insert;

1968

DROP TABLE IF EXISTS t2;

1969

CREATE TABLE t1 (a int, date_insert timestamp, PRIMARY KEY (a));

1970

INSERT INTO t1 (a) VALUES (2),(5);

1971

CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));

1972

CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET

1973

date_insert=NOW() WHERE t1.a=t2.b AND t2.a=NEW.a; END |

1974

INSERT INTO t2 (a,b) VALUES (1,2);

1975

DROP TRIGGER t_insert;

1976

CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET

1977

date_insert=NOW(),b=b+1 WHERE t1.a=t2.b AND t2.a=NEW.a; END |

1978

INSERT INTO t2 (a,b) VALUES (3,5);

1979

ERROR HY000: Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

1980

DROP TABLE t1;

1981

DROP TRIGGER t_insert;

1982

DROP TABLE t2;

1983

End of 5.0 tests

1984

drop table if exists table_25411_a;

1985

drop table if exists table_25411_b;

1986

create table table_25411_a(a int);

1987

create table table_25411_b(b int);

1988

create trigger trg_25411a_ai after insert on table_25411_a

1989

for each row

1990

insert into table_25411_b select new.*;

1991

select * from table_25411_a;

1992

1993

insert into table_25411_a values (1);

1994

ERROR 42S02: Unknown table 'new'

1995

select * from table_25411_a;

1996

1997

1998

drop table table_25411_a;

1999

drop table table_25411_b;

2000

DROP TRIGGER IF EXISTS trg;

2001

SHOW CREATE TRIGGER trg;

2002

ERROR HY000: Trigger does not exist

2003

drop table if exists t1;

2004

create table t1 (i int, j int);

2005

create trigger t1_bi before insert on t1 for each row begin end;

2006

create trigger t1_bi before insert on t1 for each row begin end;

2007

ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

2008

drop trigger t1_bi;

2009

drop trigger t1_bi;

2010

ERROR HY000: Trigger does not exist

2011

lock tables t1 read;

2012

create trigger t1_bi before insert on t1 for each row begin end;

2013

ERROR HY000: Table 't1' was locked with a READ lock and can't be updated

2014

create trigger t1_bi before insert on t1 for each row begin end;

2015

ERROR HY000: Table 't1' was locked with a READ lock and can't be updated

2016

drop trigger t1_bi;

2017

ERROR HY000: Trigger does not exist

2018

unlock tables;

2019

create trigger t1_bi before insert on t1 for each row begin end;

2020

lock tables t1 read;

2021

create trigger t1_bi before insert on t1 for each row begin end;

2022

ERROR HY000: Table 't1' was locked with a READ lock and can't be updated

2023

drop trigger t1_bi;

2024

ERROR HY000: Table 't1' was locked with a READ lock and can't be updated

2025

unlock tables;

2026

drop trigger t1_bi;

2027

lock tables t1 write;

2028

create trigger b1_bi before insert on t1 for each row set new.i = new.i + 10;

2029

insert into t1 values (10, 10);

2030

drop trigger b1_bi;

2031

insert into t1 values (10, 10);

2032

select * from t1;

2033

i j

2034

20 10

2035

10 10

2036

unlock tables;

2037

drop table t1;

2038

drop table if exists t1, t2;

2039

drop trigger if exists trg1;

2040

drop trigger if exists trg2;

2041

create table t1 (a int);

2042

create table t2 (b int);

2043

create trigger trg1 after update on t1 for each row set @a= @a+1;

2044

create trigger trg2 after update on t2 for each row set @b= @b+1;

2045

insert into t1 values (1), (2), (3);

2046

insert into t2 values (1), (2), (3);

2047

set @a= 0;

2048

set @b= 0;

2049

update t1, t2 set t1.a= t1.a, t2.b= t2.b;

2050

select @a, @b;

2051

@a @b

2052

3 3

2053

update t1, t2 set t1.a= t2.b, t2.b= t1.a;

2054

select @a, @b;

2055

@a @b

2056

6 6

2057

update t1 set a= a;

2058

select @a, @b;

2059

@a @b

2060

9 6

2061

update t2 set b= b;

2062

select @a, @b;

2063

@a @b

2064

9 9

2065

update t1 set a= 1;

2066

select @a, @b;

2067

@a @b

2068

12 9

2069

update t2 set b= 1;

2070

select @a, @b;

2071

@a @b

2072

12 12

2073

drop trigger trg1;

2074

drop trigger trg2;

2075

drop table t1, t2;

2076

CREATE TABLE t1 ( a INT, b INT );

2077

CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY, b INT );

2078

INSERT INTO t1 (a) VALUES (1);

2079

CREATE TRIGGER tr1

2080

BEFORE INSERT ON t2

2081

FOR EACH ROW

2082

BEGIN

2083

UPDATE a_nonextisting_table SET a = 1;

2084

END//

2085

CREATE TABLE IF NOT EXISTS t2 ( a INT, b INT ) SELECT a, b FROM t1;

2086

ERROR 42S02: Table 'test.a_nonextisting_table' doesn't exist

2087

SELECT * FROM t2;

2088

a b

2089

DROP TABLE t1, t2;

2090

End of 5.1 tests.

Older »