Board index » delphi » Triggers & Stored Procedure

Triggers & Stored Procedure

Hello,

        I am starting programming with Delphi & Interbase Server. And I find
something that I cannot solve.

        I have two tables: Department Table and Employee Table. Sample Metafile is
shown.

                Create Table Department (Dept_ID CHAR(3) Not Null, Dept_Name Char(30) Not
Null....)

                Create Table Employee (Emp_no Char(6) Not Null, Dept_ID Char(3)......)

        Sample Data :

        Employee Table:
                Emp_no  Dept_id
                ======  =======
                001001  001
                001002  001
                001003  001
                002001  002
                002002  002

        I want to use insert trigger or stored procedure to automatically generate
new emp_no. It is easy if I am using only a column for key field. But my
boss wish to add dept_id as a part of emp_no. As the example data, I want
to insert a new employee for "002" department, the new emp_no will be
"002003".

        How can I do that? Would you show me the example?

Best Regards,
Sydney Tang

 

Re:Triggers & Stored Procedure


Best you can do is to add an identifing Emp_No-Field into your Employee-Table
and generate the wanted field (Dept_Id and relative Emp_No) by a trigger, for
example:

Employee Table:
Emp_Id  Dept_Id  Emp_No
====== ======  ======
001        001        001001
002        001        001002
003        001        001003
004        002        002001
005        002        002002

You can generate the field Emp_No in a 'Before Insert Trigger' about the
following trigger:
CREATE TRIGGER "BF_INS_EMPLOYEE" BEFORE INSERT
        ORDER 1 ON "EMPLOYEE"
        REFERENCING NEW AS NewValues
        FOR EACH ROW
BEGIN
  declare NextEmpNo integer;
  select count(*)+1 into NextEmpNo
    from EMPLOYEE
    where (Dept_Id=NewValues.Dept_Id);
  select NewValues.DeptId||NextEmpNo into NewValues.Emp_No;
END;

Notice that this trigger is only tested under Sybase SQL Anywhere not Interbase
Server. I don't know if it runs with Interbase Server.

Christian

Re:Triggers & Stored Procedure


Sydney,

This approach will probably get you into trouble. You should really
try to talk you boss into using a simple integer for the employee
number.

For example, what will happen when:

1. an employee moves to different department? Does he or she get a new
number? Getting a new one will cause all sorts of problems; keeping
the original one brings up the question 'Why bother with the coded
number in the first place?'
2. a department gets to employee number 999? Can't hire any more? ;)
Go to four digits? which dept is number 002103? Emp 1037 in dept 002
or emp 37 in dept 21?

Employee numbers are usually used as keys to various tables. The
should be assigned sequentially, never changed, never reused. Once
assigned, that employee will always have that number, even after
terminating employment. Your method will certainly mean a lot more
work for you.

HTH
Dan
On 25 Feb 1998 10:12:36 GMT, "Sydney Tang" <ems...@hkstar.com> wrote:

Quote
>Hello,

>    I am starting programming with Delphi & Interbase Server. And I find
>something that I cannot solve.

>    I have two tables: Department Table and Employee Table. Sample Metafile is
>shown.

>            Create Table Department (Dept_ID CHAR(3) Not Null, Dept_Name Char(30) Not
>Null....)

>            Create Table Employee (Emp_no Char(6) Not Null, Dept_ID Char(3)......)

>    Sample Data :

>    Employee Table:
>            Emp_no  Dept_id
>            ======  =======
>            001001  001
>            001002  001
>            001003  001
>            002001  002
>            002002  002

>    I want to use insert trigger or stored procedure to automatically generate
>new emp_no. It is easy if I am using only a column for key field. But my
>boss wish to add dept_id as a part of emp_no. As the example data, I want
>to insert a new employee for "002" department, the new emp_no will be
>"002003".

>    How can I do that? Would you show me the example?

>Best Regards,
>Sydney Tang

--
Dan Brennand
CMDC systems, inc.
Configuration Management and Document Control: Consulting, Software,
and the only comprehesive textbook on this subject.
visit us at www.cmdcsystems.com
[Remove the SPAM from my e-mail address]

Other Threads