September 29, 2013 at 12:04 am
Guys,
I have two tables one is users and the other is frap_users ,Both contains some common attributes which are required to be same means if there is any change in the users table the same column has to updated in the frap_users and vice versa and this has to done through triggers only.When i have written triggers it is getting into infinite loop.
I will appreciate any thoughts and suggestions on this.
Am using Sql server 2008
Regards,
Papis
September 29, 2013 at 2:03 pm
Help those who want to help you. Please post the table definitions, the trigger code, along with some sample data.
To do this easily refer to the link in my signature block.
September 30, 2013 at 12:04 am
Here are the table definitions
Users table:
CREATE TABLE CORXPRADM.USERS(
USER_ID numeric(10, 0) NOT NULL,
USER_NAME nvarchar(128) NOT NULL,
DELIVERY_CHANNEL nvarchar(32) NULL,
PASSWORD varbinary(64) NULL,
USER_DISABLED nvarchar(1) NULL,
USER_DESCRIPTION nvarchar(64) NULL,
SINGLE_LOGON_ALLOWED nvarchar(1) NULL,
CREATION datetime NULL,
LAST_ACCESS datetime NULL,
ORIGINATOR numeric(10, 0) NULL,
LAST_PWD_CHANGE datetime NULL,
PWD_EXPIRES nvarchar(1) NULL,
ACCESS_FLAGS numeric(4, 0) NULL,
AGENTID numeric(8, 0) NULL,
IS_TEMPORARY nvarchar(1) NULL,
EXPIRE_COUNT numeric(2, 0) NULL,
SECURITY_COUNT numeric(2, 0) NULL,
FIRST_NAME nvarchar(20) NULL,
LAST_NAME nvarchar(30) NULL,
MIDDLE_INITIAL nvarchar(2) NULL,
PRIMARY_LOCATION_ID numeric(10, 0) NULL,
DATE_CHANGED datetime NOT NULL,
SEARCH_NAME nvarchar(128) NULL,
MAX_CHAT_SESSIONS numeric(2, 0) NULL,
LAST_LOGON datetime NULL,
EMAIL_ADDRESS nvarchar(64) NULL,
COST_CENTER nvarchar(32) NULL,
MAIL_CODE nvarchar(32) NULL,
COMPANY_CODE nvarchar(32) NULL,
IS_FLOATER nvarchar(1) DEFAULT 'N' NOT NULL,
SUPERVISOR_PWD varbinary(64) NULL,
SUPERVISOR_SECURITY_COUNT numeric(2, 0) NULL,
SUPERVISOR_DISABLED nvarchar(1) NULL,
SUPERVISORY_LEVEL numeric(3, 0) DEFAULT 0 NOT NULL,
SYNC_FLG nvarchar(1) DEFAULT 'Y' NULL
)ON corxpr_data
go
Frap_users :
CREATE TABLE SECFRPADM.FRAP_USERS(
USER_ID numeric(10, 0) NOT NULL,
LOGON_ID nvarchar(128) NOT NULL,
SRCH_LOGON_ID nvarchar(128) NOT NULL,
PASSWORD VARBINARY(64) NULL,
TIMEOUT numeric NULL,
DAYMASK nvarchar(7) NULL,
USER_STATUS nvarchar(8) NOT NULL,
DESCRIPTION nvarchar(64) NULL,
CREATION_DATE date NULL,
LAST_AUTHENTICATION_DATE date NULL,
LAST_PWD_CHANGE_DATE date NULL,
SECURITY_COUNT numeric(2, 0) NULL,
CHANGED_DATE date NOT NULL,
FIRST_NAME nvarchar(20) NULL,
LAST_NAME nvarchar(30) NULL,
MIDDLE_INITIAL nvarchar(2) NULL,
EMAIL_ADDRESS nvarchar(64) NULL,
CONTACT_PHONE nvarchar(20) NULL,
PW_POLICY_ID numeric(10, 0) NOT NULL,
PW_LOCK_TIME date NULL,
PW_EXPIRE_TIME date NULL,
PW_FAIL_TIME date NULL,
PW_GRACE_TIME date NULL,
PW_GRACE_COUNT numeric(2, 0) NULL,
PW_RESET_TIME date NULL,
PW_HISTORY VARBINARY(1024) NULL,
PW_STATUS nvarchar(16) NOT NULL,
PROPERTIES nvarchar(2000) NULL,
SYNC_FLG nvarchar(1) DEFAULT 'Y' NULL
)ON secfrp_data
go
Sample Data for users
INSERT INTO users ( user_id , user_name , delivery_channel , password , user_disabled , user_description , single_logon_allowed , creation , last_access , originator , last_pwd_change , pwd_expires , access_flags , agentid , is_temporary , expire_count , security_count , first_name , last_name , middle_initial , primary_location_id , date_changed , search_name , max_chat_sessions , last_logon , email_address , cost_center , mail_code , company_code , is_floater , supervisor_pwd , supervisor_security_count , supervisor_disabled , sync_flg )
VALUES ( 0, 'None', NULL, convert(varbinary, 'DD432F9B82807CC3DB3220449F01BC78', 2), 'N', 'Default User', 'N', '2002-10-10 00:00:00', '2002-10-10 00:00:00', 0, '2002-10-10 00:00:00', 'N', 144, 0, 'N', 0, 0, NULL, NULL, NULL, NULL, '2004-03-09 00:00:00', NULL, NULL, '2002-10-01 00:00:00', NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL, 'Y' )
GO
INSERT INTO users ( user_id , user_name , delivery_channel , password , user_disabled , user_description , single_logon_allowed , creation , last_access , originator , last_pwd_change , pwd_expires , access_flags , agentid , is_temporary , expire_count , security_count , first_name , last_name , middle_initial , primary_location_id , date_changed , search_name , max_chat_sessions , last_logon , email_address , cost_center , mail_code , company_code , is_floater , supervisor_pwd , supervisor_security_count , supervisor_disabled , sync_flg )
VALUES ( 1, 'administrator', 'desktop', convert(varbinary, 'DD432F9B82807CC3DB3220449F01BC78', 2), 'N', 'System Administrator', 'N', '2004-03-09 00:00:00', '2004-03-09 00:00:00', 1, '2004-03-09 00:00:00', 'N', 144, 0, 'N', 0, 0, 'Admin', 'Admin', 'S', NULL, '2004-03-09 00:00:00', 'ADMIN, ADMIN S', NULL, '2002-10-01 00:00:00', NULL, NULL, NULL, NULL, 'N', NULL, NULL, NULL, 'Y' )
GO
Hope This will help you
September 30, 2013 at 4:39 am
You can use CONTEXT_INFO() .. to short circuit the infinte loop.
See the pseudo code
--Trigger on one side
declare @Context varbinary
SELECT @Context = context_info()
IF @Context = 0x35353535
RETURN
ELSE
set context_info 0x35353535
--Your INSERT CODE
--Trigger on OTHER side
declare @Context2 varbinary
--select cast('1111' as varbinary)
SELECT @Context2 = context_info()
IF @Context2 = 0x31313131
RETURN
ELSE
set context_info 0x31313131
--Your INSERT CODE
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply