<manish1.agar...@gmail.com> wrote:
> 1) This will affect two tables. Account and Treatment.
> 2) Trigger is on Account for update and insert
> 3) After insert or update in Account an insert will be there in
> Treatment.
> 4) the PK or newly inserted Treatment row will be updated in Account
> table. (Account table has FK for Treatment).
> 5) Here the result is After insert or update on Account a
> corresponding insert happens in Treatment Table but the problem is
> that ...Account Table's FK is not getting updated.
>
> Here is my Trigger. Pls help. This is not a specific case and u can
> tell me any simple example. For security reason I cant post Table
> scripts.
>
> Thanx a lot.
> -Manish
>
> If Exists ( SELECT name
> FROM sysobjects
> WHERE name =3D 'I_ACCOUNT_DEFAULT_METHD'
> AND type =3D 'TR')
> DROP TRIGGER I_ACCOUNT_DEFAULT_METHD
> /
> CREATE TRIGGER I_ACCOUNT_DEFAULT_METHD ON ACCOUNT AFTER INSERT,
> UPDATE
> AS
> BEGIN
> SET NOCOUNT ON
>
> DECLARE
> @column_acc_value$1 NUMERIC(15),
> @column_new_value$1 FLOAT,
> @column_treatment_schedule_id NUMERIC(15)
>
> DECLARE ForEachInsertedRowTriggerCursor CURSOR LOCAL FORWARD_ONLY
> READ_ONLY FOR
> SELECT ACCOUNT_ID, EXT_NUMBER_17, TREATMENT_SCHEDULE_ID FROM
> inserted
>
> OPEN ForEachInsertedRowTriggerCursor
> FETCH NEXT FROM ForEachInsertedRowTriggerCursor INTO @column_acc_=
value
> $1, @column_new_value$1, @column_treatment_schedule_id
>
> WHILE @@fetch_status =3D 0
>
> BEGIN
>
> DECLARE @var_treatment_schedule_id NUMERIC(15)
> DECLARE @var_primary_contact_method NVARCHAR(10)
> DECLARE @old_primary_contact_method NVARCHAR(10)
>
> IF @column_new_value$1 IS NOT NULL
>
> BEGIN
> IF @column_new_value$1 =3D 1
> BEGIN
> SELECT @var_primary_contact_method =3D 'Email'
> END
>
> IF @column_new_value$1 =3D 2
> BEGIN
> SELECT @var_primary_contact_method =3D 'Fax'
> END
>
> IF @column_new_value$1 =3D 3
> BEGIN
> SELECT @var_primary_contact_method =3D 'Phone'
> END
>
> IF @column_new_value$1 =3D 4
> BEGIN
> SELECT @var_primary_contact_method =3D 'PrintFil=
e'
> END
>
> IF @column_treatment_schedule_id IS NULL
>
> BEGIN
>
> Exec IdGeneratorSeq @var_treatment_schedule_id O=
UTPUT
>
> INSERT INTO TREATMENT_SCHEDULE
> (TREATMENT_SCHEDULE_ID ,
> SUPPRESS_ALL ,
> SEND_AUTO_BILLING_STMT ,
> PRIMARY_CONTACT_METHOD ,
> UPDATE_DATE ,
> UPDATE_SEQUENCE )
> VALUES (@var_treatment_schedule_id ,
> 'N' ,
> 'N' ,
> @var_primary_contact_method ,
> GETDATE() ,
> 1
> )
>
> UPDATE ACCOUNT
> SET TREATMENT_SCHEDULE_ID =3D @column_treatme=
nt_schedule_id,
> UPDATE_SEQUENCE =3D UPDATE_SEQUENCE + 1,
> UPDATE_DATE =3D GETDATE()
> WHERE ACCOUNT_ID =3D @column_acc_value$1
>
> END
> ELSE
> BEGIN
>
> SELECT @old_primary_contact_method =3D PRIMARY_CONTACT_ME=
THOD
> FROM TREATMENT_SCHEDULE
> WHERE TREATMENT_SCHEDULE_ID =3D @column_treatment_schedu=
le_id
>
> END
>
> IF @old_primary_contact_method !=3D @var_primary_contact_=
method
>
> BEGIN
> UPDATE TREATMENT_SCHEDULE
> SET PRIMARY_CONTACT_METHOD =3D @var_primary_c=
ontact_method,
> UPDATE_SEQUENCE =3D UPDATE_SEQUENCE + 1,
> UPDATE_DATE =3D GETDATE()
> WHERE TREATMENT_SCHEDULE_ID =3D @column_treatme=
nt_schedule_id
>
> END
> END
> FETCH NEXT FROM ForEachInsertedRowTriggerCursor INTO
> @column_acc_value$1, @column_new_value$1,
> @column_treatment_schedule_id
> END
> CLOSE ForEachInsertedRowTriggerCursor
> DEALLOCATE ForEachInsertedRowTriggerCursor
>
> END
> /

<manish1.agar...@gmail.com> wrote:
> I dont know what you are talking about. Please elaborate.
> Where i can find out this setting "are the database recursive trigger
> been set on? "\
>
> Thanx for your kind help.

On Apr 20, 6:07 pm, xyb <xiangyua...@gmail.com> wrote:
> On 4=E6=9C=8821=E6=97=A5, =E4=B8=8A=E5=8D=888=E6=97=B645=E5=88=86, "manis=
h1.agar...@gmail.com"<manish1.agar...@gmail.com> wrote:
> > I dont know what you are talking about. Please elaborate.
> > Where i can find out this setting "are the database recursive trigger
> > been set on? "\
>
> > Thanx for your kind help.
>
> My test code here may help you.
> USE sssb
> IF DATABASEPROPERTYEX('sssb','IsRecursiveTriggersEnabled') =3D 0
> BEGIN
> alter database sssb
> set RECURSIVE_TRIGGERS ON
> END
>
> create table ttt
> (
> c1 int,
> c2 varchar(10)
> )
>
> insert into ttt
> select 1,'aaa'
>
> create trigger tri_nested
> on ttt
> after insert
> as
> insert into ttt
> select * from inserted
>
> go
>
> select * from ttt
>
> drop trigger tri_nested
> drop table ttt

> 1) This will affect two tables. Account and Treatment.
> 2) Trigger is on Account for update and insert
> 3) After insert or update in Account an insert will be there in
> Treatment.
> 4) the PK or newly inserted Treatment row will be updated in Account
> table. (Account table has FK for Treatment).
> 5) Here the result is After insert or update on Account a
> corresponding insert happens in Treatment Table but the problem is
> that ...Account Table's FK is not getting updated.

> IF @column_treatment_schedule_id IS NULL
> BEGIN
> Exec IdGeneratorSeq @var_treatment_schedule_id OUTPUT
> INSERT INTO TREATMENT_SCHEDULE
> (TREATMENT_SCHEDULE_ID ,
> SUPPRESS_ALL ,
> SEND_AUTO_BILLING_STMT ,
> PRIMARY_CONTACT_METHOD ,
> UPDATE_DATE ,
> UPDATE_SEQUENCE )
> VALUES (@var_treatment_schedule_id ,
> 'N' ,
> 'N' ,
> @var_primary_contact_method ,
> GETDATE() ,
> 1
> )
>
> UPDATE ACCOUNT
> SET TREATMENT_SCHEDULE_ID = @column_treatment_schedule_id,
> UPDATE_SEQUENCE = UPDATE_SEQUENCE + 1,
> UPDATE_DATE = GETDATE()
> WHERE ACCOUNT_ID = @column_acc_value$1