CODEWITHSUNDEEP
sqltriggerssqlanywhere
Rick77
Rick77

Reputation: 241

Trigger performing insert

I have to create a trigger that does not allow more than one null in a certain column (hourfinish). Here is the trigger I wrote (I am using Sybase):

ALTER TRIGGER "InsertIntoCardDetail" instead of insert on
DBA.CardDetail
referencing new as new_name
for each row
begin
  declare @nullCount integer;
  if(new_name.hourfinish is null) then
    select COUNT(*) into @nullCount
      from CardDetail
      where hourfinish is null;
    if(@nullCount > 0) then
      raiserror 99999 'Cannot have Multiple Nulls'
    else
      insert into CardDetail( card,hourstart,hourfinish,"work",project,N) 
        values( new_name.card,new_name.hourstart,new_name.hourfinish,new_name."work",new_name.project,new_name.N) 
    end if
  else
    insert into CardDetail( card,hourstart,hourfinish,"work",project,N) 
      values( new_name.card,new_name.hourstart,new_name.hourfinish,new_name."work",new_name.project,new_name.N) 
  end if
end

The trigger works fine. What I am asking is if there is a command executing the insert - a command that can replace those long insert statements.

Upvotes: 2

Views: 1273

Answers (2)

Rick77
Rick77

Reputation: 241

I achieved the same result with a before insert trigger. This way i don't need to write any insert statement in the trigger. 

ALTER TRIGGER "InsertIntoCardDetail" before insert on
DBA.CardDetail
referencing new as new_name
for each row
begin
  if(new_name.hourfinish is null)
    and exists(select 1
      from CardDetail
      where hourfinish is null) then
    raiserror 99999 'Cannot have Multiple Nulls'
  end if;
end

Upvotes: 0

Robert
Robert

Reputation: 25763

Try to change your trigger to:

ALTER TRIGGER "InsertIntoCardDetail" instead of insert on
DBA.CardDetail
referencing new as new_name
for each row
begin
   if(new_name.hourfinish is null) then    
    if exists 
    (select 1
      from CardDetail
      where hourfinish is null) then
      raiserror 99999 'Cannot have Multiple Nulls'
      return 1
    end if --exists 
   end if--(new_name.hourfinish is null) 

    insert into CardDetail( card,hourstart,hourfinish,"work",project,N) 
    values(new_name.card,new_name.hourstart,new_name.hourfinish,new_name."work",new_name.project,new_name.N) 

end

Upvotes: 1

Related Questions