Table2 on a field Field1 (primary key in Table1). This trigger doesn't allow me to put a null value in the field Field1 in table Table2. I want to modify th trigger to make this possible. How to modify the following trigger ====================================== CREATE TRIGGER T_Table2_UTrig ON [Table2] FOR UPDATE AS SET NOCOUNT ON IF UPDATE(Field1) BEGIN IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM Table1, inserted WHERE (Table1.Champ1 = inserted.Field1)) BEGIN RAISERROR 44446 'bla bla bla...' ROLLBACK TRANSACTION END END ===================================== Thanks for any help [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <#Cw$8KJQDHA.1556@TK2MSFTNGP10.phx.gbl> [ref] => [htmlstate] => on_nl2br [postusername] => mchd [ip] => mchd@wanadoo.fr [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) -->Table2 on a field[/quote] > Field1[quote] > > (primary key in Table1). This trigger doesn't allow me to put a null[/quote][/quote] value[quote][quote] > > in the field Field1 in table Table2. I want to modify th trigger to make > > this possible. How to modify the following trigger > > > > ====================================== > > CREATE TRIGGER T_Table2_UTrig ON [Table2] FOR UPDATE AS > > SET NOCOUNT ON > > > > IF UPDATE(Field1) > > BEGIN > > IF (SELECT COUNT(*) FROM inserted) != > > (SELECT COUNT(*) FROM Table1, inserted WHERE (Table1.Champ1 = > > inserted.Field1)) > > BEGIN > > RAISERROR 44446 'bla bla bla...' > > ROLLBACK TRANSACTION > > END > > END > > ===================================== > > > > Thanks for any help > > > >[/quote] > >[/quote] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => <#Cw$8KJQDHA.1556@TK2MSFTNGP10.phx.gbl> [htmlstate] => on_nl2br [postusername] => mchd [ip] => mchd@wanadoo.fr [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) -->Table2 on a field[/quote] > > Field1[quote] > > > (primary key in Table1). This trigger doesn't allow me to put a null[/quote][/quote] > value[quote][quote] > > > in the field Field1 in table Table2. I want to modify th trigger to[/quote][/quote][/quote] make[quote][quote][quote] > > > this possible. How to modify the following trigger > > > > > > ====================================== > > > CREATE TRIGGER T_Table2_UTrig ON [Table2] FOR UPDATE AS > > > SET NOCOUNT ON > > > > > > IF UPDATE(Field1) > > > BEGIN > > > IF (SELECT COUNT(*) FROM inserted) != > > > (SELECT COUNT(*) FROM Table1, inserted WHERE (Table1.Champ1[/quote][/quote][/quote] =[quote][quote][quote] > > > inserted.Field1)) > > > BEGIN > > > RAISERROR 44446 'bla bla bla...' > > > ROLLBACK TRANSACTION > > > END > > > END > > > ===================================== > > > > > > Thanks for any help > > > > > >[/quote] > > > >[/quote] > >[/quote] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => <#Cw$8KJQDHA.1556@TK2MSFTNGP10.phx.gbl> [htmlstate] => on_nl2br [postusername] => SriSamp [ip] => ssampath@sct.co [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> TransactSQL problem - Microsoft SQL / MS SQL Server

TransactSQL problem - Microsoft SQL / MS SQL Server

Hi all, This is my problem if someone can help me. I have an update trigger betwin two tables Table1-->Table2 on a field Field1 (primary key in Table1). This trigger doesn't allow me to put a null value in the field Field1 in table Table2. I want to modify th trigger to make this possible. How to modify the following trigger ====================================== CREATE TRIGGER T_Table2_UTrig ON [Table2] FOR UPDATE AS SET NOCOUNT ON IF UPDATE(Field1) BEGIN IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM Table1, inserted WHERE (Table1.Champ1 = inserted.Field1)) BEGIN RAISERROR 44446 'bla bla bla...' ROLLBACK TRANSACTION END ...

  1. #1

    Default TransactSQL problem

    Hi all,

    This is my problem if someone can help me.

    I have an update trigger betwin two tables Table1-->Table2 on a field Field1
    (primary key in Table1). This trigger doesn't allow me to put a null value
    in the field Field1 in table Table2. I want to modify th trigger to make
    this possible. How to modify the following trigger

    ======================================
    CREATE TRIGGER T_Table2_UTrig ON [Table2] FOR UPDATE AS
    SET NOCOUNT ON

    IF UPDATE(Field1)
    BEGIN
    IF (SELECT COUNT(*) FROM inserted) !=
    (SELECT COUNT(*) FROM Table1, inserted WHERE (Table1.Champ1 =
    inserted.Field1))
    BEGIN
    RAISERROR 44446 'bla bla bla...'
    ROLLBACK TRANSACTION
    END
    END
    =====================================

    Thanks for any help


    mchd Guest

  2. #2

    Default Re: TransactSQL problem

    Thanks for the answer. Null values are allowed for the field. If the trigger
    is deleted you can put null values in the field.

    "SriSamp" <ssampathsct.co.in> a écrit dans le message de
    news:eJ5FgPJQDHA.2316TK2MSFTNGP11.phx.gbl...
    > This trigger does not say anything about not being able to insert a NULL
    > into Table2. Is the code of the actual trigger??? Not being able to insert
    > NULL might mean that Table2 has this column defined as non-nullable. You
    > might want to change that and not the trigger.
    > --
    > HTH,
    > SriSamp
    > Please reply to the whole group only!
    >
    > "mchd" <mchdwanadoo.fr> wrote in message
    > news:%23Cw$8KJQDHA.1556TK2MSFTNGP10.phx.gbl...
    > > Hi all,
    > >
    > > This is my problem if someone can help me.
    > >
    > > I have an update trigger betwin two tables Table1-->Table2 on a field
    > Field1
    > > (primary key in Table1). This trigger doesn't allow me to put a null
    value
    > > in the field Field1 in table Table2. I want to modify th trigger to make
    > > this possible. How to modify the following trigger
    > >
    > > ======================================
    > > CREATE TRIGGER T_Table2_UTrig ON [Table2] FOR UPDATE AS
    > > SET NOCOUNT ON
    > >
    > > IF UPDATE(Field1)
    > > BEGIN
    > > IF (SELECT COUNT(*) FROM inserted) !=
    > > (SELECT COUNT(*) FROM Table1, inserted WHERE (Table1.Champ1 =
    > > inserted.Field1))
    > > BEGIN
    > > RAISERROR 44446 'bla bla bla...'
    > > ROLLBACK TRANSACTION
    > > END
    > > END
    > > =====================================
    > >
    > > Thanks for any help
    > >
    > >
    >
    >

    mchd Guest

  3. #3

    Default Re: TransactSQL problem

    Then, the trigger logic is probably wrong. The two COUNT's that it is
    checking is probably not matching and the transaction is rolled back?? Can
    you put some stub PRINT statements in the IF block and check to see if it is
    firing??
    --
    HTH,
    SriSamp
    Please reply to the whole group only!

    "mchd" <mchdwanadoo.fr> wrote in message
    news:exAUnZJQDHA.2052TK2MSFTNGP11.phx.gbl...
    > Thanks for the answer. Null values are allowed for the field. If the
    trigger
    > is deleted you can put null values in the field.
    >
    > "SriSamp" <ssampathsct.co.in> a écrit dans le message de
    > news:eJ5FgPJQDHA.2316TK2MSFTNGP11.phx.gbl...
    > > This trigger does not say anything about not being able to insert a NULL
    > > into Table2. Is the code of the actual trigger??? Not being able to
    insert
    > > NULL might mean that Table2 has this column defined as non-nullable. You
    > > might want to change that and not the trigger.
    > > --
    > > HTH,
    > > SriSamp
    > > Please reply to the whole group only!
    > >
    > > "mchd" <mchdwanadoo.fr> wrote in message
    > > news:%23Cw$8KJQDHA.1556TK2MSFTNGP10.phx.gbl...
    > > > Hi all,
    > > >
    > > > This is my problem if someone can help me.
    > > >
    > > > I have an update trigger betwin two tables Table1-->Table2 on a field
    > > Field1
    > > > (primary key in Table1). This trigger doesn't allow me to put a null
    > value
    > > > in the field Field1 in table Table2. I want to modify th trigger to
    make
    > > > this possible. How to modify the following trigger
    > > >
    > > > ======================================
    > > > CREATE TRIGGER T_Table2_UTrig ON [Table2] FOR UPDATE AS
    > > > SET NOCOUNT ON
    > > >
    > > > IF UPDATE(Field1)
    > > > BEGIN
    > > > IF (SELECT COUNT(*) FROM inserted) !=
    > > > (SELECT COUNT(*) FROM Table1, inserted WHERE (Table1.Champ1
    =
    > > > inserted.Field1))
    > > > BEGIN
    > > > RAISERROR 44446 'bla bla bla...'
    > > > ROLLBACK TRANSACTION
    > > > END
    > > > END
    > > > =====================================
    > > >
    > > > Thanks for any help
    > > >
    > > >
    > >
    > >
    >
    >

    SriSamp Guest

Similar Threads

  1. contribute problem - access denied file may not existpermission problem
    By Al1973 in forum Macromedia Contribute Connection Administrtion
    Replies: 6
    Last Post: September 17th, 04:16 PM
  2. Replies: 2
    Last Post: September 30th, 12:13 PM
  3. Replies: 0
    Last Post: August 23rd, 11:56 AM
  4. Replies: 0
    Last Post: August 2nd, 03:03 PM
  5. Replies: 2
    Last Post: July 17th, 07:27 AM

Bookmarks

Posting Permissions

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