The Heap™ – Consultancy ©®

ErikE

5:20 AM

Is my answer here off-base? stackoverflow.com/a/35491211/57611

Philᵀᴹ

6:39 AM

First person to get the gold Oracle badge :) dba.stackexchange.com/help/badges/223/oracle

7

ErikE

6:59 AM

@Philᵀᴹ Congratulations!

Eugene

7:18 AM

Hello everyone!

Andriy M

7:35 AM

@Philᵀᴹ Congrats! Finally another proper database tag badge :)

ypercubeᵀᴹ

8:09 AM

@ErikE It's fine.

Regrading "there is no need to join": No, but sometimes it's more efficient to join. There is no need to not join either ;)

Similar question, with lots of different ways to achieve the same result: How to filter SQL results in a has-many-through relation

ErikE

8:39 AM

@ypercubeᵀᴹ agree about joining, just that in this particular case it was likely to be more joins for more positions, and that seemed less ideal.

@ypercubeᵀᴹ the reason I asked is because someone down voted and I wondered if I was missing anything.

@ypercubeᵀᴹ haven't read your link yet, but a more set-based approach with FULL JOIN would be a more general-purpose solution.

Tom V

@Philᵀᴹ Nice

Andriy M

@ErikE OP's case could probably be generalised beyond recognition, but there's no need for that either :)

ErikE

With just 2 and finding those that are supersets of the 2 is easy. Finding n with only exact matches is harder.

@AndriyM not sure what's prompting you to say this... do you mean that my expectation of wanting more items, thus generalizing a bit, was not ideal?

Andriy M

8:55 AM

@ErikE I mean I'm not sure why you are talking about FULL JOIN. Usually if this kind of problem is solved with a join or joins, it's inner joins, not full joins. I can imagine how a full join might be appropriate but that would be generalising the problem more than necessary for that specific question.

@ErikE Oh, I'm sorry, didn't realise that comment was meant for a different question than the one you answered.

dezso

@Eugene hi

Eugene

10:17 AM

Guys, is there anyway to determine what is the order of triggers, that is executed on same table on same event? As I understand amount is not limited. Thank you.

dezso

@Eugene which RDBMS?

Eugene

Oracle

Sorry. Forgot to mention PlSQL

ypercubeᵀᴹ

I don't see how a FULL JOIN would help.

As AndriyM pointed, there are several ways to do this, usually with either JOINs or EXISTS subqueries or with the GROUP BY method you used.

SOme methods are easier to generalize, like GROUP BY. The joins and exists are not easy to generalize but are usually faster (in the DBMS I checked, ie Postgres and MySQL). I'd expect SQL Server ot behave the same.

But performance depends on mnay things, like distributions of values, indexes, etc.

dezso

@Eugene triggers will fire in some order (the documentation goes as far to say "you cannot control the order")

ypercubeᵀᴹ

And the exact division problem is harder still, as you pointed out.

dezso

10:27 AM

asktom.oracle.com/pls/apex/…

if you are after to tell in which order they were fired for a given event, some logging could possibly help

but we have a gold-badge @Philᵀᴹ who can possibly explain it better

ypercubeᵀᴹ

@ErikE I've seen a 20-times self-join perform slightly faster than a single GROUP BY ;)

Eugene

10:42 AM

@dezso I see. The issue is, that I have a trigger, that is table level (if I understand correctly) and it does some operations. I need to use the result of that operations in my case, but I'm not sure whether it will be available during my trigger or not hens the order of execution. And my trigger is FOR EACH ROW.

ypercubeᵀᴹ

@Eugene So you have 2 triggers, one at statement level and one at row level?

Eugene

Yes.

ypercubeᵀᴹ

The order is defined in that case.

50

A: row-level trigger vs statement-level trigger

The main difference is not what can be modified by the trigger, that depends on the DBMS. A trigger (row or statement level) may modify one or many rows, of the same or other tables as well and may have cascading effects (trigger other actions/triggers) but all these depend on the DBMS of course....

Philᵀᴹ

"Oracle fires multiple triggers in an unspecified, random order, if more than one trigger of the same type exists for a given statement; that is, triggers of the same type for the same statement are not guaranteed to fire in any specific order."

In reality, it does it in object_id order usually, but don't rely on it

ypercubeᵀᴹ

@Philᵀᴹ I mean it is defined if one trigger is statement level and the other is row-level. Am I wrong?

Philᵀᴹ

10:48 AM

"First, statement level triggers are fired, and then row level triggers are fired."

Eugene

Ou. Okay.

Philᵀᴹ

docs.oracle.com/cd/B19306_01/server.102/b14220/…

Balazs Papp

@Eugene Use the FOLLOWS/PRECEEDS trigger_ordering_clause docs.oracle.com/database/122/LNPLS/…

Eugene

chat.stackexchange.com/transcript/message/33916296#33916296 that covers my question.

Philᵀᴹ

@BalazsPapp New in 12c?

Balazs Papp

10:50 AM

@Philᵀᴹ available since 11.1

Andriy M

The AskTom article appears to be putting the order of different types like this: 1) statement-level BEFORE, 2) row-level BEFORE, 3) row-level AFTER, 4) statement-level AFTER.

ypercubeᵀᴹ

@Philᵀᴹ That is for BEFORE triggers. Isn't it the opposite for AFTER triggers?

@BalazsPapp see this ^^

Am I wrong? Because I'd have to update my answer at SO, which claims the same as AndriyM (I probably read the AskTom article when I answered that)

@Philᵀᴹ where it says:

> Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking:

Run all BEFORE statement triggers that apply to the statement.

Loop for each row affected by the SQL statement.

Run all BEFORE row triggers that apply to the statement.

Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.)

Run all AFTER row triggers that apply to the statement.

Complete deferred integrity constraint checking.

It would be illogical to run an AFTER stetement trigger before AFTER row triggers anyway. Because the statement hasn't finished until all rows have been processed.

Philᵀᴹ

Yeah, the docs are clear

Jack Douglas

12:21 PM

@ypercubeᵀᴹ "The Oracle 11g trigger syntax now includes the FOLLOWS clause to guarantee execution order for triggers defined with the same timing point"

the list you have above is a list of 'timing points'

ypercubeᵀᴹ

@JackDouglas Yes, ok for that. I was asking for triggers with not the same timing point

Jack Douglas

in which case you can't use FOLLOWS

which I think you know

ypercubeᵀᴹ

2 hours ago, by Philᵀᴹ

"First, statement level triggers are fired, and then row level triggers are fired."

Was trying to clarify this (in case someone considers it with AFTER triggers)

Jack Douglas

@BalazsPapp I don't think PRECEEDS does the same thing

Andriy M

> Use FOLLOWS to indicate that the trigger being created must fire after the specified triggers. [...] Use PRECEDES to indicate that the trigger being created must fire before the specified triggers.

Jack Douglas

12:26 PM

or rather it is only for a very special case: stackoverflow.com/questions/17693502/…

Andriy M

(That was from the docs)

Jack Douglas

"You can specify PRECEDES only for a reverse crossedition trigger"

Andriy M

"reverse crossedition trigger" – more than 60% of that is absolutely unknown to me. :)

Oh, crossedition – as in Oracle Database editions.

Jack Douglas

editions are supposed to be a very powerful tool for reducing downtime during releases, I've never actually used them

asktom.oracle.com/pls/apex/…

ypercubeᵀᴹ

@AndriyM No

I think they refer to edition of a table. I'm sure Jack will correct me.

Jack Douglas

12:31 PM

edition applies to the whole database afaik

Andriy M

So I was right, that was something unfamiliar for me.

ypercubeᵀᴹ

@JackDouglas Very interesting. So it's meant to be used mainly for DDL changes, to have minimal downtime.

Jack Douglas

yes, and the reverse crossedition triggers are for the case when you want to run both the new application and old application at the same time for a period during migration, so you can move over gradually. I guess there is some obvious reason why that requires a PRECEEDS syntax, but it escapes me.

dezso

someone should make a Hungarian notation joke to @BalazsPapp, so that I am not the only victim of these

2

ypercubeᵀᴹ

12:47 PM

2

Hungarian language

Proposed Q&A site for people interested in the confusing Hungarian language.

Currently in definition.

> confusing Hungarian language.

Andriy M

@dezso Perhaps he hasn't hung out with us long enough to qualify for Hungarian jokes :)

ypercubeᵀᴹ

@BalazsPapp your profile link to the WP blog is not working.

dezso

I just managed to scare a systems administrator of ours with a quick query for reverting order of items in a string

Andriy M

@dezso By replacing ASC with DESC?

Eugene

1:33 PM

Thank you all who helped with question related to trigger.

dezso

@AndriyM splitting the string using a regexp to an array, revert that, fix some encoding and so on

Andriy M

@dezso I can imagine something like that to be an eye-opener for someone who might not realise that writing a query may involve a number of skills.

Philᵀᴹ

2:54 PM

@ypercubeᵀᴹ that was in the summary section of the documentation. I just did a quick cut and paste without thinking. Further down in the docs, it says otherwise - the part you quoted after

ypercubeᵀᴹ

3:19 PM

Are you affiliated with the product? Please disclose, if so. And add that it is a commercial one, not a free one. — ypercubeᵀᴹ 3 hours ago

ErikE

3:35 PM

@ypercubeᵀᴹ Sure, multiple or many seeks can often beat 1 scan.

@ypercubeᵀᴹ regarding FULL JOIN I'll show you what I mean at some point if I can.

Evan Carroll

4:13 PM

HALLO, AND HAPPY MONDAY EVERYONE.

How is my channel doing?

ConcernedOfTunbridgeWells

@dezso So, what type is a @Balasz?

dezso

@ConcernedOfTunbridgeWells e_parse_error

Balazs it is

ConcernedOfTunbridgeWells

5:42 PM

@dezso Exception @BalazsException = new ParseError()

Sami

8:02 PM

Hello all

Thomas Ward

@Sami greetings!

Sami

:) thank you

i have a question

can i ask?

about sql server jobs and sql server agent

@ThomasWard

I have around 40 different sql server jobs in one instance. They all have different schedules. Some run once a day some every two mins some every five mins. If I have a need to stop sql server agent, how can I find the best time when no jobs are running so I won't interrupt any of my jobs?

Transcript for

The Heap™ – Consultancy ©®