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

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'statement set @x= 1' at line 2

141

drop trigger trg_1;

142

143

Testcase 3.5.1.3:

144

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

145

CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';

146

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on tb3 BEFORE INSERT for each row set new.f120 = 't'' at line 1

147

CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's';

148

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's'' at line 1

149

CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row;

150

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set @ret1 = 'test' for each row' at line 1

151

CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row;

152

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE AFTER on tb3 set @ret1 = 'test' for each row' at line 1

153

CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test';

154

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test'' at line 1

155

drop trigger trg3_1;

156

drop trigger trg3_2;

157

drop trigger trg3_3;

158

drop trigger trg3_4;

159

drop trigger trg3_5;

160

161

Testcase: 3.5.1.5:

162

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

163

CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';

164

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on tb3 for each row set new.f120 = 'e'' at line 1

165

CREATE TRIGGER trg4_2 INSERT on tb3 for each set row new.f120 = 'f';

166

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT on tb3 for each set row new.f120 = 'f'' at line 1

167

CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g';

168

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tb3 for each row set new.f120 = 'g'' at line 1

169

CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g';

170

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set new.f120 = 'g'' at line 1

171

CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g';

172

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g'' at line 1

173

CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g';

174

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'for each row set new.f120 = 'g'' at line 1

175

drop trigger trg4_1;

176

drop trigger trg4_2;

177

drop trigger trg4_3;

178

drop trigger trg4_4;

179

drop trigger trg4_5;

180

drop trigger trg4_6;

181

182

Testcase 3.5.1.6: - Need to fix

183

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

184

185

Testcase 3.5.1.7: - need to fix

186

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

187

drop table if exists t1;

188

Warnings:

189

Note 1051 Unknown table 't1'

190

create table t1 (f1 int, f2 char(25),f3 int) engine = <engine_to_be_used>;

191

CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1

192

for each row set new.f3 = '14';

193

CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ

194

BEFORE UPDATE on test.t1 for each row set new.f3 = '42';

195

ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long

196

CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX

197

BEFORE UPDATE on test.t1 for each row set new.f3 = '42';

198

insert into t1 (f2) values ('insert 3.5.1.7');

199

select * from t1;

200

f1 f2 f3

201

NULL insert 3.5.1.7 14

202

update t1 set f2='update 3.5.1.7';

203

select * from t1;

204

f1 f2 f3

205

NULL update 3.5.1.7 42

206

select trigger_name from information_schema.triggers order by trigger_name;

207

trigger_name

208

gs_insert

209

trg5_1

210

trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX

211

ts_insert

212

drop trigger trg5_1;

213

drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ;

214

ERROR 42000: Identifier name 'trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' is too long

215

drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX;

216

drop table t1;

217

218

Testcase 3.5.1.8:

219

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

220

CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't';

221

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* before insert on tb3 for each row set new.f120 = 't'' at line 1

222

CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't';

223

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'trigger before insert on tb3 for each row set new.f120 = 't'' at line 1

224

CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't';

225

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '100 before insert on tb3 for each row set new.f120 = 't'' at line 1

226

CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't';

227

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@@view before insert on tb3 for each row set new.f120 = 't'' at line 1

228

CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't';

229

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@name before insert on tb3 for each row set new.f120 = 't'' at line 1

230

CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3

231

for each row set new.f120 ='X';

232

ERROR HY000: Trigger in wrong schema

233

drop database if exists trig_db;

234

create database trig_db;

235

use trig_db;

236

create table t1 (f1 integer) engine = <engine_to_be_used>;

237

use test;

238

CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3

239

for each row set @ret_trg6_2 = 5;

240

ERROR 42S02: Table 'trig_db.tb3' doesn't exist

241

use trig_db;

242

CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3

243

for each row set @ret_trg6_3 = 18;

244

ERROR HY000: Trigger in wrong schema

245

use test;

246

drop database trig_db;

247

drop trigger trg6_1;

248

drop trigger trg6_3;

249

250

Testcase 3.5.1.9:(cannot be inplemented at this point)

251

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

252

253

Testcase 3.5.1.10:

254

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

255

CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X';

256

CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';

257

ERROR HY000: Trigger already exists

258

drop trigger trg7_1;

259

260

Testcase 3.5.1.?:

261

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

262

drop table if exists t1;

263

drop table if exists t2;

264

create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;

265

create table t2 (f1 char(50), f2 integer) engine = <engine_to_be_used>;

266

create trigger trig before insert on t1

267

for each row set new.f1 ='trig t1';

268

create trigger trig before update on t2

269

for each row set new.f1 ='trig t2';

270

ERROR HY000: Trigger already exists

271

insert into t1 value ('insert to t1',1);

272

select * from t1;

273

f1 f2

274

trig t1 1

275

update t1 set f1='update to t1';

276

select * from t1;

277

f1 f2

278

update to t1 1

279

insert into t2 value ('insert to t2',2);

280

update t2 set f1='update to t1';

281

select * from t2;

282

f1 f2

283

update to t1 2

284

drop table t1;

285

drop table t2;

286

drop trigger trig;

287

288

Testcase 3.5.1.11:

289

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

290

drop database if exists trig_db1;

291

drop database if exists trig_db2;

292

drop database if exists trig_db3;

293

create database trig_db1;

294

create database trig_db2;

295

create database trig_db3;

296

use trig_db1;

297

create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;

298

create trigger trig before insert on t1

299

for each row set new.f1 ='trig1', @test_var1='trig1';

300

use trig_db2;

301

create table t2 (f1 char(50), f2 integer) engine = <engine_to_be_used>;

302

create trigger trig before insert on t2

303

for each row set new.f1 ='trig2', @test_var2='trig2';

304

use trig_db3;

305

create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;

306

create trigger trig before insert on t1

307

for each row set new.f1 ='trig3', @test_var3='trig3';

308

set @test_var1= '', @test_var2= '', @test_var3= '';

309

use trig_db1;

310

insert into t1 (f1,f2) values ('insert to db1 t1',1);

311

insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2);

312

insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3);

313

insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4);

314

select @test_var1, @test_var2, @test_var3;

315

@test_var1 @test_var2 @test_var3

316

trig1 trig2 trig3

317

select * from t1 order by f2;

318

f1 f2

319

trig1 1

320

trig1 2

321

select * from trig_db2.t2;

322

f1 f2

323

trig2 3

324

select * from trig_db3.t1;

325

f1 f2

326

trig3 4

327

select * from t1 order by f2;

328

f1 f2

329

trig1 1

330

trig1 2

331

use test;

332

drop database trig_db1;

333

drop database trig_db2;

334

drop database trig_db3;

335

336

Testcase 3.5.2.1/2/3:

337

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

338

drop database if exists trig_db1;

339

drop database if exists trig_db2;

340

create database trig_db1;

341

create database trig_db2;

342

use trig_db1;

343

create table t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;

344

create table trig_db2.t1 (f1 char(50), f2 integer) engine = <engine_to_be_used>;

345

create trigger trig1_b before insert on t1

346

for each row set @test_var1='trig1_b';

347

create trigger trig_db1.trig1_a after insert on t1

348

for each row set @test_var2='trig1_a';

349

create trigger trig_db2.trig2 before insert on trig_db2.t1

350

for each row set @test_var3='trig2';

351

select trigger_schema, trigger_name, event_object_table

352

from information_schema.triggers

353

where trigger_schema like 'trig_db%'

354

order by trigger_name;

355

trigger_schema trigger_name event_object_table

356

trig_db1 trig1_a t1

357

trig_db1 trig1_b t1

358

trig_db2 trig2 t1

359

set @test_var1= '', @test_var2= '', @test_var3= '';

360

insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);

361

insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);

362

select @test_var1, @test_var2, @test_var3;

363

@test_var1 @test_var2 @test_var3

364

trig1_b trig1_a trig2

365

drop database trig_db1;

366

drop database trig_db2;

367

DROP TABLE test.tb3;

Older »