Skip to content
Advertisement

Trigger to update a table whenever there is an insert

I am trying to use a trigger (in SQL Server) to update a table whenever there is an insert on the table but I get an error:

Conversion failed when converting date and/or time from character string.

Trigger used:

create trigger [dbo].[update_table_scan_for_insert] 
on [dbo].[table_scan] 
for insert 
as    
begin
    update table_scan set
        start_date = getdate()
    where start_date = 'NULL'
end 

The table table_scan is to be updated when there is NULL in start_date after an insert happens.

Advertisement

Answer

Use IS NULL instead of 'NULL' in SQL Server.

create trigger [dbo].[update_table_scan_for_insert] 
on [dbo].[table_scan] 
for insert 
as    
begin
    update table_scan set
        start_date = getdate()
    where start_date IS NULL
end

Also you can try this where ISNULL(start_date,'NULL') ='NULL'

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement