Implementing Triggers in 4D

• Download source - 3.42 KB

Introduction

4D(4^th dimension) is a cross-platform relational database management system that facilitates building excellent database and web applications. This development tool is owned by 4D, Inc and has been there since 1984. 4^th dimension supports a wide range of standards, including ODBC, a native HTTP server, FastCGI, XML, and SOAP.

In this article, I will be discussing the concept of triggers in 4D and how to implement them in 4D applications.

By now, you must be wondering if the trigger in 4D is something different or equivalent to SQL triggers. Let me tell you that the definition is quite similar to what we find in the SQL world, however they differ a lot in the way they are implemented.

Triggers in 4D are methods associated with table that are used to control and validate actions that affect records or the table as a whole.

Features specific to 4D triggers are that they execute on the machine where the database engine runs. They return an error code, or 0 if there is no problem. Only one trigger can run at a time.

The triggers tab of the table properties dialog in 4D lists four different events:

1. On saving new record
2. On saving an existing record
3. On deleting a record
4. On loading a record

These are called database events or record modification events.

Format to write a trigger:

This is a sample of what a trigger looks like:

` [Employee] Trigger 
C_LONGINT($0;$ErrorCode) 
$ErrorCode:=0 

Case of: 
(Database event=Save Existing Record Event) 
$ErrorCode:= EmployeeSaveExisting 
: (Database event=Save New Record Event) 
$ErrorCode:= EmployeeSaveNew 
: (Database event=Delete Record Event) 
$ErrorCode:= EmployeeDelete 
: (Database event=Load Record Event) 
Else ` A bug. 
$ErrorCode:=-1 
End case 
$0:=$ErrorCode 

Let me take the opportunity to explain to you about cascading triggers in 4D. Look at the explanation below:

When a trigger performs actions that invoke another table's trigger, it is called a trigger cascade. Trigger cascades are necessary to avoid orphaned records in the system.

For example, consider the following case:

Suppose there are three tables say [table1], [table2] and [table3].

When you delete a [table1] record, its trigger deletes all related [table2] records. The [table2] trigger in turn deletes all related [table3] records. This is how cascading triggers are meant to behave.

Now suppose there is a fourth table, say [table4] which is dependant on a field from the [table3]. The [table3] record is deleted so when we do an update, then the [table4] is going to behave hazardously.

The problem can be easily solved in 4D. In 4D language, we have command (called Trigger level) that provides us the ability to handle the situation. The command lets us know where we stand. The first trigger executed is level 1, the second is level 2, and so on.

If, for example, you wanted to say that [table3] records can only be deleted by the [table2] trigger, then the [table3] trigger would include code like this:

C_LONGINT($0;$errorCode) 
Case of:(Database event=Delete Record Event) 
` Deletion is allowed only from the [table2] trigger. 
I f(Trigger level<2) 
` Return an error code: 
$errorCode:=-1 
Else 
` Get the properties of the invoking trigger (Trigger level - 1). 
TRIGGERPROPERTIES(Triggerlevel-1;$invokingEvent;$invokingTableNum;$recordNum) 
` delete record trigger 
$TableNum:=Table(->[table2]) 
I f($invokingEvent#Delete Record Event) | ($TableNum#$invokingTableNum) 
` return an error. 
$errorCode:=-2 
Else 
` Carry on the deletion. 
$errorCode:=0 
End if 
` ($invokingEvent#Delete Record Event)| ($TableNum #$invokingTableNum) 
End if 
` (Trigger level<2) 
End case 
$0:=$errorCode 

Summary

This article provides a better insight about using triggers in 4D. The novice users can easily understand and use triggers for different database events.

Manjushree Das
www.mindfiresolutions.com

History

• 10^th March, 2007: Initial post