5

I want to insert the current time automatically with the inserted date in datetime column. by default it inserts 00:00:00

I created the trigger

Create trigger tr_tm on emp
after insert,update
as
declare @tme time

set @tme=(select CONVERT(varchar(7),start_date,108) from emp)

update emp
set @tme=convert(varchar(8),getdate(),108)
where @tme='00:00:00'
go

but it shows error:

Msg 512, Level 16, State 1, Procedure tr_te, Line 15 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

how it is possible??

Thanks in advance..

il_guru
  • 8,383
  • 2
  • 42
  • 51
user2988456
  • 51
  • 1
  • 2
  • 1
    This is SQL server, so why is it tagged MySQL? – tadman Apr 29 '16 at 10:17
  • 1
    add default contstraint with value `GETDATE()` – Backs Apr 29 '16 at 10:18
  • 1
    Single most common error with SQL Server triggers is to assume that `inserted` and `deleted` only contain one row. Second most common - not *using* the `inserted` and `deleted` tables, which tell you *specifically* which row(s) have just been affected. – Damien_The_Unbeliever Apr 29 '16 at 11:34

1 Answers1

0

One approach is to add a default constraint to your column. The default constraint will be used to populate the column, when you do not provide a value.

Example

-- Example table.
CREATE TABLE Example
    (
        A   INT,
        B   TIME            DEFAULT(CAST(GETDATE() AS TIME))
    )
;

When inserting data the default will be applied, if we don't supply a value.

-- Adding data, without referencing column.
INSERT INTO Example
    (
        A
    )
VALUES
    (1),
    (2),
    (3)
;

You can also explicitly specify that you want the default value to be applied.

-- Adding data, using DEFAULT.
INSERT INTO Example
    (
        A,
        B
    )
VALUES
    (10, DEFAULT),
    (20, DEFAULT),
    (30, DEFAULT)       
;

You also retain the option of ignoring the default and supplying your own value.

-- Adding data without using default value.
INSERT INTO Example
    (
        A,
        B
    )
VALUES
    (100, '09:30:00'),
    (200, '10:30:00'),
    (300, '11:30:00')       
;

The three queries above returned:

A   B
1   13:19:28.1900000
2   13:19:28.1900000
3   13:19:28.1900000
10  13:19:28.1900000
20  13:19:28.1900000
30  13:19:28.1900000
100 09:30:00.0000000
200 10:30:00.0000000
300 11:30:00.0000000
David Rushton
  • 4,915
  • 1
  • 17
  • 31