Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003

    Unanswered: Tedious newbie problems

    My project is halted on 2 niggling problems, any help would be much appreciated from those to which this is childsplay:

    Problem 1: ______________________

    I have a table with employee data, and another table containing their pay information.

    Create table Payroll_tbl
    (payroll_no char(6),
    employee_no char(6),
    Weekends integer not null default 0,
    constraint pay_key primary key (payroll_no, employee_no),
    constraint maintain_pay foreign key (employee_no) references employee_tbl on update cascade on delete cascade,
    constraint chk_weekend check (Weekends in (0, 1)),

    the line above regarding on update and on delete does not seem sufficient so that when I add a new employee to the employee_tbl that the new employee's number appears automatically as a new record in Payroll_tbl, with the weekends field defaulting as 0, and to generate a new payroll_no accordingly. This is what I desire, is there any simple way of achieveing this? I would also like to add their employee no to another table about projects in a similar manner.
    Is it something called a trigger? i am new to sql :|

    Problem 2: _______________________

    I have defined something as 'Expense smallmoney,' in my table, no constraints. And then I add a value like '217' or '725.00' when inserting data. Yet the query analyser returns an error saying 'Disallowed implicit conversion from data type varchar to data type smallmoney, table '. I'm not telling it to do so! It also says use the convert function to run this query. I really dont understand I just want some currency values to appear. ie 259.85

    Thanks In Advance

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: Tedious newbie problems

    1) Yes, a trigger will do what you want. The syntax of triggers varies between products, but would be somthing like this (Oracle syntax):

    create or replace trigger emp_trg after insert on employee_tbl
    for each row
      insert into payroll_tbl( employee_no, weekends, ... )
      values ( :new.employee_no, 0, ... );
    (BTW This "_tbl" suffix everyone uses these days really irritates me. I know it's a bloody table, why make me type "_tbl" all the time? Grrr...)

    2) Can't help with this, obviously you are not using Oracle which is all I know.

  3. #3
    Join Date
    Jan 2004

    For problem #2, would I be correct in assuming Expense is the name of the field and smallmoney is the datatype?

    You are probably inserting the numerical data in quotes, which would give you this error message. Numerical data need not be inserted in quotes.

    create table #t
    (Expense smallmoney)

    insert into #t

    select * from #t


    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts