Reports

Triggers are PL/SQL code, which gets executed automatically.
We have five types of triggers in Oracle reports.
1) Before Parameter form
2) After Parameter form
3) Before report
4) Between pages
5) After report

We cannot create new type of trigger.
Report triggers must explicitly return TRUE or FALSE
By looking at the names of the trigger, we understand at what point trigger is executed ( fired ).

Let us start understanding Before report and After report triggers

Open the report builder tool Use the report wizard OK Next Create both Web and Paper layout Next Style - Tabular Next SQL Query   Data Source definition -    select * from emp; Next provide username, password and database Connect Move all the columns from Available Fields to Displayed Fields Next Next Next Predefined Template - Blue Next Finish

This is how the report look like

Go to Tools Object Navigator   Report Triggers Right click on BEFORE REPORT PL/SQL Editor

Provide the following code

function BeforeReport return boolean is
begin
  srw.message(10, 'Welcome to my Report');
  return (TRUE);

end;

Click on compile.
You should get a message "Successfully compiled"

Click on close.

SRW -- is a built-in package
message --  is a procedure, used to  display a message with number and text.

We are done with creating BEFORE REPORT trigger.
Now , let us create AFTER REPORT trigger.

Go to Tools Object Navigator  Report Triggers Right click on AFTER REPORT PL/SQL Editor

Provide the following code

function AfterReport return boolean is
begin
  srw.message (15, 'End of the report');
  return (TRUE);

end;

Click on compile.
You should get a message "Successfully compiled"

Click on close.

Save the report as EMP3.rdf

Run the report.

BEFORE REPORT triggers fires, we get the following output

Click on OK

We get the Report output.

Click on Close button.
Observe, AFTER REPORT trigger is not executed.
To experience AFTER REPORT trigger,  Go to File Print Preview

As expected, BEFORE REPORT trigger is executed, we get the following output

Click on OK

We get the report in the Previewer window.

Click on Close button in the previewer window.

After report trigger gets executed. We get the following output

Let us now experience BEFORE PARAMETER FORM and AFTER PARAMETER FORM triggers.
Open the report builder tool Use the report wizard OK Next Create both Web and Paper layout Next Style - Tabular Next SQL Query   Data Source definition -    select * from emp where deptno = :deptno ; Next provide username, password and database Connect Move all the columns from Available Fields to Displayed Fields Next Next Next Predefined Template - Blue Next Finish

We get Runtime parameter form, waiting for the user input

Enter the value  10

We get the following output

Go to Tools Object Navigator  Report Triggers Right click on AFTER PARAMETER FORM

PL/SQL Editor

Provide the following code

function AfterPForm return boolean is
begin
  if :deptno is null then
                :deptno :=10;
                srw.message ( 12, 'Parameter not provided - default value assigned');
  return (TRUE);
  else
                return ( TRUE );
  end if;
 
end;
Click on compile.             
You should get a message "Successfully compiled"

Click on close.

As, we know, the above code is executed, after runtime parameter form is displayed.
If the end user, do not pass any value, the value 10 is assigned to the parameter.

Save the report as EMP4.rdf
Run the report.

As expected, Runtime parameter form is opened.

Do not pass any value. Just press enter key.
The code in the after parameter form trigger is executed.

We get the following message

Click on OK
We get the employees working for deptno 10.

Run the report one more time.
This time enter value 20 for the parameter.

We get the employees working for deptno 20

Similarly , try with BEOFRE PARAMETER FORM trigger and experience it.