You must Sign In to post a response.
  • Category: ASP.NET

    Dynamic sql in trigger

    I have one update triger on table1,which insert updated values into table2.If any column in trable1 contain null value,does not insert record into table2.What shall I do in such kind of situation.I am using dynamic sql in trigger.
  • #709819
    Hi,
    You can control this from business logic side of the application.
    Or if you want to do from the Sqlserver trigger, you can avoid the insertion in table2 by adding the Where clause in the query of table2 insertion.
    Add the where clause as follows
    WHERE empID is not null .

    Thanks
    Padma

    Have a good Day

  • #709824
    Hi,

    Can you post the code here? It seems you are not fetching the value if there is a null value which you need to change. By looking at your trigger we may be able to suggest you what change you need to make.


    Regards,
    Asheej T K

  • #709956
    table 1 :- sname(varchar(10)),age(int),bdate(datetime)
    table2 : sname(varchar(10)),age(int),bdate(datetime),address(varchar(50))


    Update trigger ::
    create trigger update_table on table1
    after update
    as
    begin

    declare @sname varchar(10)
    declare @age varchar(10)
    declare @bdate varchar(10)
    declare @sql varchar(4000)

    set @sname = (select sname from inserted)
    set @age = (select age from inserted)
    set @bdate = (select bdate from inserted)
    set @sql = 'insert into table2(sname,age,bdate) values('+@sname+','+@age+','+@bdate+')'

    exec (@sql)
    end

  • #709964
    Hi,

    Use the validation in your business logic or check where clause in your sql before insertion for null value.

    Regards
    Raj

  • #709967
    when age contain null or bdate contain null vaue.Record can not be inserted into second table.So I checked like this set @age = (select isnull(age,'null') from inserted).But I am getting syntax error while converting varchar value null to int.

  • #709995
    Hi,

    CREATE TRIGGER EMP_SAL_CHECK ON Employee AFTER INSERT
    AS
    DECLARE @sal INT
    BEGIN
    SELECT @sal=salary from inserted
    if(@sal<5000)
    begin
    rollback transaction
    raiserror('Salary less than 5000',16,1)
    end
    END

    insert into Employee values('santhosh',3000,'A')

    Regards,
    Kalandiyur Subramanian Mohan
    www.mohanks.com


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.