Update Statements in a Trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update Statements in a Trigger

Author

Topic

nans04
Starting Member

20 Posts

Posted - 2004-08-20 : 09:47:15

I need urgent help from your experts. Please help with code.

I want to write a trigger on bankrel - upon insert, update, or delete records. The trigger code has to update fields in rmp table.

Column "_internid" is unique to tables bankrel and rmp

Trigger code fetch values of columns: _bank, _assets, _ change_date from table bankrel, sort them by assests in descending order (largest assets first) within/for each _internid, selects only top four banks within/for each intern_id and bankrel._active = 'Y'.

Bankrel table can also have less than four banks for a _internid. But it can select only maximum top 4 banks for each _interid.

Code then, updates corresponding columns in rmp table,
1. looks for _internid of rmp ie. rmp_internid = bankrel._internid and updates columns in rmp for that _internid:
_sow_bank2, _sow_money2, _sow_date2,_sow_bank3, _sow_money3, _sow_date3,_sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5
3. Suppose there are no values to be updated, say there are only 2 banks for a _internid from table bankrel, the code updates rmp _sow_bank2, _sow_money2, _sow_bank3, _sow_money3.
and other fields are left as it is.

rmp table columns
_sow_bank is varchar
_sow_money is float
_sow_date is smalldatetime
_internid is int

bankrel table columns
_bank is varchar
_assets is varchar
_change_date is smalldatetime
_internid is int

Please help.thanks so much

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-20 : 13:11:04

Could you provide CREATE TABLE statements for your tables, INSERT INTO statements for some sample data, what you want the data to look like after the INSERT and also after the trigger fires? Then we'll be able to help you out.

Tara

nans04
Starting Member

20 Posts

Posted - 2004-08-20 : 14:43:22

Shoot!! Table is too big

CREATE TABLE [dbo].[RMP] (
[_INTERNID] [int] NOT NULL ,
[_CHGDATE] [smallint] NOT NULL ,
[_CHGTIME] [smallint] NOT NULL ,
[_STAMP2] [binary] (8) NOT NULL ,
[_ID] [varchar] (9) NOT NULL ,
[_ID2] [varchar] (9) NOT NULL ,
[_CREATE_DATE] [smalldatetime] NOT NULL ,
[_CHANGED_BY] [varchar] (8) NOT NULL ,
[_CHANGED_DATE] [smalldatetime] NOT NULL ,
[_YEAR] [float] NOT NULL ,
[_STATUS1] [varchar] (8) NOT NULL ,
[_BUS_SPON] [varchar] (25) NOT NULL ,
[_CREATE_W] [char] (1) NOT NULL ,
[_CREATE_WO] [char] (1) NOT NULL ,
[_CREATE_WF] [char] (1) NOT NULL ,
[_CREATE_WOF] [char] (1) NOT NULL ,
[_SEGMENT] [varchar] (10) NOT NULL ,
[_STATUS2] [varchar] (8) NOT NULL ,
[_TP] [varchar] (3) NOT NULL ,
[_SC_POTEN] [varchar] (8) NOT NULL ,
[_PRIO1] [varchar] (30) NOT NULL ,
[_PRIO2] [varchar] (30) NOT NULL ,
[_PRIO3] [varchar] (30) NOT NULL ,
[_PO_NNM1] [char] (1) NOT NULL ,
[_PO_NNM2] [char] (1) NOT NULL ,
[_PO_NNM3] [char] (1) NOT NULL ,
[_BS_FOC1] [varchar] (30) NOT NULL ,
[_BS_FOC2] [varchar] (30) NOT NULL ,
[_BS_FOC3] [varchar] (30) NOT NULL ,
[_BS_DETA] [text] NOT NULL ,
[_BS_APPR] [text] NOT NULL ,
[_IS_FOC1] [varchar] (30) NOT NULL ,
[_IS_FOC2] [varchar] (30) NOT NULL ,
[_IS_FOC3] [varchar] (30) NOT NULL ,
[_IS_DETA] [text] NOT NULL ,
[_IS_APPR] [text] NOT NULL ,
[_MCF_FOC1] [varchar] (30) NOT NULL ,
[_MCF_FOC2] [varchar] (30) NOT NULL ,
[_MCF_FOC3] [varchar] (30) NOT NULL ,
[_MCF_DETA] [text] NOT NULL ,
[_MCF_APPR] [text] NOT NULL ,
[_REM_FIM] [text] NOT NULL ,
[_NEX_FIM] [text] NOT NULL ,
[_SOW_BANK1] [varchar] (40) NULL ,
[_SOW_BANK2] [varchar] (40) NULL ,
[_SOW_BANK3] [varchar] (40) NULL ,
[_SOW_BANK4] [varchar] (40) NULL ,
[_SOW_BANK5] [varchar] (40) NULL ,
[_SOW_BANK6] [varchar] (40) NULL ,
[_SOW_MONEY1] [float] NULL ,
[_SOW_MONEY2] [float] NULL ,
[_SOW_MONEY3] [float] NULL ,
[_SOW_MONEY4] [float] NULL ,
[_SOW_MONEY5] [float] NULL ,
[_SOW_MONEY6] [float] NULL ,
[_SOW_MONEY7] [float] NULL ,
[_SOW_MONEY8] [float] NULL ,
[_SOW_DATE1] [varchar] (17) NULL ,
[_SOW_DATE2] [varchar] (20) NULL ,
[_SOW_DATE3] [varchar] (20) NULL ,
[_SOW_DATE4] [varchar] (20) NULL ,
[_SOW_DATE5] [varchar] (20) NULL ,
[_SOW_DATE6] [varchar] (20) NULL ,
[_ST1_RTN] [varchar] (8) NOT NULL ,
[_ST2_RTN] [varchar] (8) NOT NULL ,
[_BUS_RTN] [varchar] (25) NOT NULL ,
[_KEY_RTN] [varchar] (7) NOT NULL ,
[_IN_NET] [varchar] (6) NOT NULL ,
[_PP_FOC1] [varchar] (30) NOT NULL ,
[_PP_FOC2] [varchar] (30) NOT NULL ,
[_PP_FOC3] [varchar] (30) NOT NULL ,
[_PP_DETA] [text] NOT NULL ,
[_PP_APPR] [text] NOT NULL ,
[_CC_FOC1] [varchar] (30) NOT NULL ,
[_CC_FOC2] [varchar] (30) NOT NULL ,
[_CC_FOC3] [varchar] (30) NOT NULL ,
[_CC_DETA] [text] NOT NULL ,
[_CC_APPR] [text] NOT NULL ,
[_IN_FOC1] [varchar] (30) NOT NULL ,
[_IN_FOC2] [varchar] (30) NOT NULL ,
[_IN_FOC3] [varchar] (30) NOT NULL ,
[_IN_DETA] [text] NOT NULL ,
[_IN_APPR] [text] NOT NULL ,
[_MCR_FOC1] [varchar] (30) NOT NULL ,
[_MCR_FOC2] [varchar] (30) NOT NULL ,
[_MCR_FOC3] [varchar] (30) NOT NULL ,
[_MCR_DETA] [text] NOT NULL ,
[_MCR_APPR] [text] NOT NULL ,
[_REM_RTN] [text] NOT NULL ,
[_NEX_RTN] [text] NOT NULL ,
[_SQL] [char] (1) NOT NULL ,
[_CHANGE_DATE_M1] [varchar] (3) NOT NULL ,
[_CHANGE_DATE_M2] [varchar] (3) NOT NULL

CREATE TABLE [dbo].[SITE] (
[_INTERNID] [int] NOT NULL ,
[_CHGDATE] [smallint] NOT NULL ,
[_CHGTIME] [smallint] NOT NULL ,
[_STAMP1] [binary] (8) NOT NULL ,
[_SITEDATE] [smallint] NOT NULL ,
[_SITETIME] [smallint] NOT NULL ,
[__NAME] [varchar] (60) NOT NULL ,
[_ID] [varchar] (9) NOT NULL ,
[_NAME] [varchar] (60) NOT NULL ,
[_SITET] [varchar] (1) NOT NULL ,
[_SIRET_NO] [varchar] (14) NOT NULL ,
[_HEADQUARTERS] [char] (1) NOT NULL ,
[_HEADQUARTERS_ID] [varchar] (9) NOT NULL ,
[_E_GROUP] [varchar] (40) NOT NULL ,
[_LANGUAGE] [varchar] (2) NOT NULL ,
[_LOCATION] [varchar] (15) NOT NULL ,
[_OU_TEAM_ALL_HLP] [varchar] (40) NOT NULL ,
[_OU_TEAM_ALL] [varchar] (80) NOT NULL ,
[_DESK_HLP] [varchar] (40) NOT NULL ,
[_DESK] [varchar] (80) NOT NULL ,
[_MAIN_RLSHIP_MGR] [varchar] (8) NOT NULL ,
[_MAIN_MGR_SIGN] [char] (1) NOT NULL ,
[_MAIN_MGR_DEPUTY] [varchar] (8) NOT NULL ,
[_MAIN_MGR_DEPUTY_SIGN] [char] (1) NOT NULL ,
[_CO_RLSHIP_MGR1] [varchar] (8) NOT NULL ,
[_CO_MGR1_SIGN] [char] (1) NOT NULL ,
[_CO_MGR1_DEPUTY] [varchar] (8) NOT NULL ,
[_CO_MGR1_DEPUTY_SIGN] [char] (1) NOT NULL ,
[_CO_RLSHIP_MGR2] [varchar] (8) NOT NULL ,
[_CO_MGR2_SIGN] [char] (1) NOT NULL ,
[_CO_MGR2_DEPUTY] [varchar] (8) NOT NULL ,
[_CO_MGR2_DEPUTY_SIGN] [char] (1) NOT NULL ,
[_CO_RLSHIP_MGR3] [varchar] (8) NOT NULL ,
[_CO_MGR3_SIGN] [char] (1) NOT NULL ,
[_CO_MGR3_DEPUTY] [varchar] (8) NOT NULL ,
[_CO_MGR3_DEPUTY_SIGN] [char] (1) NOT NULL ,
[_CO_RLSHIP_MGR4] [varchar] (8) NOT NULL ,
[_CO_MGR4_SIGN] [char] (1) NOT NULL ,
[_CO_MGR4_DEPUTY] [varchar] (8) NOT NULL ,
[_CO_MGR4_DEPUTY_SIGN] [char] (1) NOT NULL ,
[_CO_RLSHIP_MGR5] [varchar] (8) NOT NULL ,
[_CO_MGR5_SIGN] [char] (1) NOT NULL ,
[_CO_MGR5_DEPUTY] [varchar] (8) NOT NULL ,
[_CO_MGR5_DEPUTY_SIGN] [char] (1) NOT NULL ,
[_KEY_ACCOUNT_MGR] [varchar] (8) NOT NULL ,
[_MAIN_AAT_RLSHIP_MGR] [varchar] (8) NOT NULL ,
[_MAIN_AAT_MGR_SPEC] [varchar] (20) NOT NULL ,
[_MAIN_AAT_RLSHIP_DPT] [varchar] (8) NOT NULL ,
[_MAIN_AAT_DPT_SPEC] [varchar] (20) NOT NULL ,
[_CO_AAT_RLSHIP_MGR] [varchar] (8) NOT NULL ,
[_CO_AAT_MGR_SPEC] [varchar] (20) NOT NULL ,
[_CO_AAT_RLSHIP_DPT] [varchar] (8) NOT NULL ,
[_CO_AAT_DPT_SPEC] [varchar] (20) NOT NULL ,
[_CO_AAT_RLSHIP_MGR2] [varchar] (8) NOT NULL ,
[_CO_AAT_MGR2_SPEC] [varchar] (20) NOT NULL ,
[_CO_AAT_RLSHIP_DPT2] [varchar] (8) NOT NULL ,
[_CO_AAT_DPT2_SPEC] [varchar] (20) NOT NULL ,
[_IM_TYPE] [varchar] (3) NOT NULL ,
[_IM_STATUS] [varchar] (1) NOT NULL ,
[_IM_STATUS_TIMESTAMP] [varchar] (14) NOT NULL ,
[_IM_STATUS_TIME_HLP] [varchar] (14) NOT NULL ,
[_QUALIFICATION] [varchar] (3) NOT NULL ,
[_QUALIFICATION_OLD] [varchar] (3) NOT NULL ,
[_QUALI_CHANGEDATE] [smalldatetime] NOT NULL ,
[_QUALI_CHANGEWHO] [varchar] (8) NOT NULL ,
[_INTERMED_CODE] [varchar] (4) NOT NULL ,
[_MASTER_NO] [varchar] (14) NOT NULL ,
[_RAS_ID] [varchar] (18) NOT NULL ,
[_GIC] [varchar] (5) NOT NULL ,
[_OLY_ID_FIM_COMPANY] [varchar] (7) NOT NULL ,
[_HR_URL] [varchar] (80) NOT NULL ,
[_REG_BODY] [varchar] (80) NOT NULL ,
[_LIC_NO] [varchar] (15) NOT NULL ,
[_LIC_DATE] [datetime] NOT NULL ,
[_COPY_OF_LIC_REC] [char] (1) NOT NULL ,
[_ASSOCIATION] [varchar] (80) NOT NULL ,
[_MEMBER_SINCE] [smallint] NOT NULL ,
[_SEGMENT_ORIGINAL] [varchar] (2) NOT NULL ,
[_SEGMENT_EU] [varchar] (2) NOT NULL ,
[_SEGMENT] [varchar] (2) NOT NULL ,
[_POTENTIAL_AUM] [float] NOT NULL ,
[_POTENTIAL_GROSS_REV] [int] NOT NULL ,
[_POTENTIAL_A1] [char] (1) NOT NULL ,
[_POTENTIAL_A2] [char] (1) NOT NULL ,
[_POTENTIAL_A3] [char] (1) NOT NULL ,
[_POTENTIAL_A4] [char] (1) NOT NULL ,
[_POTENTIAL_B] [char] (1) NOT NULL ,
[_POTENTIAL_C] [char] (1) NOT NULL ,
[_POTENTIAL_D] [char] (1) NOT NULL ,
[_FREI1] [varchar] (80) NOT NULL ,
[_FREI2] [varchar] (80) NOT NULL ,
[_FREI3] [varchar] (80) NOT NULL ,
[_FREI4] [varchar] (80) NOT NULL ,
[_POTENTIALBY] [varchar] (8) NOT NULL ,
[_POTENTIALDATE] [smalldatetime] NOT NULL ,
[_PROFIT_RATING] [varchar] (2) NOT NULL ,
[_IM_AUM] [int] NOT NULL ,
[_IM_AUM_YEAR] [smallint] NOT NULL ,
[_IM_KPI] [varchar] (10) NOT NULL ,
[_SPEC_MARKETS] [varchar] (40) NOT NULL ,
[_DOM_STREET] [varchar] (40) NOT NULL ,
[_DOM_COUNTRY] [varchar] (35) NOT NULL ,
[_DOM_COUNTY] [varchar] (20) NOT NULL ,
[_DOM_POSTALCODE] [varchar] (10) NOT NULL ,
[_DOM_CITY] [varchar] (40) NOT NULL ,
[_SAME_ADDRESS] [char] (1) NOT NULL ,
[_POS_PO_BOX] [varchar] (30) NOT NULL ,
[_POS_PO_POSTALCODE] [varchar] (10) NOT NULL ,
[_POS_PO_CITY] [varchar] (40) NOT NULL ,
[_MAIL_ADDRESS_HEADER] [varchar] (30) NOT NULL ,
[_MAIL_NAME] [varchar] (60) NOT NULL ,
[_MAIL_SUPPLEMENT] [varchar] (40) NOT NULL ,
[_MAIL_STREET_POBOX] [varchar] (50) NOT NULL ,
[_MAIL_COUNTY] [varchar] (20) NOT NULL ,
[_MAIL_COUNTRY] [varchar] (35) NOT NULL ,
[_MAIL_POSTALCODE] [varchar] (10) NOT NULL ,
[_MAIL_CITY] [varchar] (40) NOT NULL ,
[_PHONE] [varchar] (25) NOT NULL ,
[_PHONE2] [varchar] (25) NOT NULL ,
[_PHONE3] [varchar] (25) NOT NULL ,
[_FAX] [varchar] (25) NOT NULL ,
[_E_MAIL1] [varchar] (80) NOT NULL ,
[_E_MAIL2] [varchar] (80) NOT NULL ,
[_COMP_URL] [varchar] (80) NOT NULL ,
[_SALUTATION_COMMON] [varchar] (40) NOT NULL ,
[_MAILSTOP] [char] (1) NOT NULL ,
[_MAIL_NUMBERED_ACC] [char] (1) NOT NULL ,
[_UBS_PUBLIC] [varchar] (80) NOT NULL ,
[_COMMENT] [varchar] (80) NOT NULL ,
[_COMMENT2] [text] NOT NULL ,
[_CREATE_ALERT] [char] (1) NOT NULL ,
[_ALERT_MESSAGE] [varchar] (60) NOT NULL ,
[_ALERT_DATE] [smalldatetime] NOT NULL ,
[_ALERT_AUDIENCE] [varchar] (40) NOT NULL ,
[_CREATED_BY] [varchar] (8) NOT NULL ,
[_CREATE_DATE] [smalldatetime] NOT NULL ,
[_CHANGED_BY] [varchar] (8) NOT NULL ,
[_CHANGE_DATE] [smalldatetime] NOT NULL ,
[_UPDATE_WHO] [varchar] (8) NOT NULL ,
[_UPDATE_WHEN] [smalldatetime] NOT NULL ,
[_ALL_MANAGERS] [varchar] (50) NOT NULL ,
[_ALL_AAT_MANAGERS] [varchar] (40) NOT NULL ,
[_MANUAL_MAIL_ADDRESS] [smallint] NOT NULL ,
[_ADDR_CHANGE] [char] (1) NOT NULL ,
[_STATUS_BL] [char] (1) NOT NULL ,
[_JAHR_1] [varchar] (10) NOT NULL ,
[_JAHR_2] [varchar] (10) NOT NULL ,
[_JAHR_3] [varchar] (10) NOT NULL ,
[_JAHR_4] [varchar] (10) NOT NULL ,
[_JAHR_5] [varchar] (10) NOT NULL ,
[_JAHR_6] [varchar] (10) NOT NULL ,
[_RAS_PERIOD] [varchar] (17) NOT NULL ,
[_RAS_CURRENCY] [varchar] (3) NOT NULL ,
[_RAS_Q1_GRANDTOTAL] [int] NOT NULL ,
[_RAS_Q2_GRANDTOTAL] [int] NOT NULL ,
[_RAS_Q3_GRANDTOTAL] [int] NOT NULL ,
[_RAS_Q4_GRANDTOTAL] [int] NOT NULL ,
[_RAS_SUM] [int] NOT NULL ,
[_RAS_UPDATE_DATE] [smalldatetime] NOT NULL ,
[_JAHR_1_QUARTAL_1] [int] NOT NULL ,
[_JAHR_1_QUARTAL_2] [int] NOT NULL ,
[_JAHR_1_QUARTAL_3] [int] NOT NULL ,
[_JAHR_1_QUARTAL_4] [int] NOT NULL ,
[_JAHR_2_QUARTAL_1] [int] NOT NULL ,
[_JAHR_2_QUARTAL_2] [int] NOT NULL ,
[_JAHR_2_QUARTAL_3] [int] NOT NULL ,
[_JAHR_2_QUARTAL_4] [int] NOT NULL ,
[_JAHR_3_QUARTAL_1] [int] NOT NULL ,
[_JAHR_3_QUARTAL_2] [int] NOT NULL ,
[_JAHR_3_QUARTAL_3] [int] NOT NULL ,
[_JAHR_3_QUARTAL_4] [int] NOT NULL ,
[_JAHR_4_QUARTAL_1] [int] NOT NULL ,
[_JAHR_4_QUARTAL_2] [int] NOT NULL ,
[_JAHR_4_QUARTAL_3] [int] NOT NULL ,
[_JAHR_4_QUARTAL_4] [int] NOT NULL ,
[_JAHR_5_QUARTAL_1] [int] NOT NULL ,
[_JAHR_5_QUARTAL_2] [int] NOT NULL ,
[_JAHR_5_QUARTAL_3] [int] NOT NULL ,
[_JAHR_5_QUARTAL_4] [int] NOT NULL ,
[_JAHR_6_QUARTAL_1] [int] NOT NULL ,
[_JAHR_6_QUARTAL_2] [int] NOT NULL ,
[_JAHR_6_QUARTAL_3] [int] NOT NULL ,
[_JAHR_6_QUARTAL_4] [int] NOT NULL ,
[_JAHR_1_SUMME] [int] NOT NULL ,
[_JAHR_2_SUMME] [int] NOT NULL ,
[_JAHR_3_SUMME] [int] NOT NULL ,
[_JAHR_4_SUMME] [int] NOT NULL ,
[_JAHR_5_SUMME] [int] NOT NULL ,
[_JAHR_6_SUMME] [int] NOT NULL ,
[_TARIFF] [varchar] (80) NOT NULL ,
[_MIS_CURRENCY] [varchar] (3) NOT NULL ,
[_MIS_REPORT] [varchar] (80) NOT NULL ,
[_MIS_REPORT1] [varchar] (80) NOT NULL ,
[_MIS_REPORT2] [varchar] (80) NOT NULL ,
[_MIS_REPORT3] [varchar] (80) NOT NULL ,
[_MIS_GROSSREVENUE_Q1] [int] NOT NULL ,
[_MIS_GROSSREVENUE_Q2] [int] NOT NULL ,
[_MIS_GROSSREVENUE_Q3] [int] NOT NULL ,
[_MIS_GROSSREVENUE_Q4] [int] NOT NULL ,
[_MIS_GROSSREVENUE_SUM] [int] NOT NULL ,
[_MIS_GROSSREVENUE_AVG] [int] NOT NULL ,
[_MIS_NETREVENUE_Q1] [int] NOT NULL ,
[_MIS_NETREVENUE_Q2] [int] NOT NULL ,
[_MIS_NETREVENUE_Q3] [int] NOT NULL ,
[_MIS_NETREVENUE_Q4] [int] NOT NULL ,
[_MIS_NETREVENUE_SUM] [int] NOT NULL ,
[_POT_YEAR_BEGIN] [float] NOT NULL ,
[_POT_EURO] [float] NOT NULL ,
[_PHASE_EURO] [varchar] (20) NOT NULL ,
[_RESULT_EURO] [float] NOT NULL ,
[_MIS_NETREVENUE_AVG] [int] NOT NULL ,
[_MIS_GROSSROA_Q1] [float] NOT NULL ,
[_MIS_GROSSROA_Q2] [float] NOT NULL ,
[_MIS_GROSSROA_Q3] [float] NOT NULL ,
[_MIS_GROSSROA_Q4] [float] NOT NULL ,
[_MIS_GROSSROA_AVERAGE] [float] NOT NULL ,
[_MIS_NETROA_Q1] [float] NOT NULL ,
[_MIS_NETROA_Q2] [float] NOT NULL ,
[_MIS_NETROA_Q3] [float] NOT NULL ,
[_MIS_NETROA_Q4] [float] NOT NULL ,
[_MIS_NO_OF_CLIENTS_1] [float] NOT NULL ,
[_MIS_NO_OF_CLIENTS_2] [float] NOT NULL ,
[_MIS_NO_OF_CLIENTS_3] [float] NOT NULL ,
[_MIS_NO_OF_CLIENTS_4] [float] NOT NULL ,
[_MIS_NETROA_AVERAGE] [float] NOT NULL ,
[_MIS_QUARTER_1] [varchar] (17) NOT NULL ,
[_MIS_QUARTER_2] [varchar] (17) NOT NULL ,
[_MIS_QUARTER_3] [varchar] (17) NOT NULL ,
[_MIS_QUARTER_4] [varchar] (17) NOT NULL ,
[_MIS_AUM_Q1] [float] NOT NULL ,
[_MIS_AUM_Q2] [float] NOT NULL ,
[_MIS_AUM_Q3] [float] NOT NULL ,
[_MIS_AUM_Q4] [float] NOT NULL ,
[_MIS_AUM_AVERAGE] [float] NOT NULL ,
[_MIS_AUM_POTENTIAL] [float] NOT NULL ,
[_MIS_GRROA_POTENTIAL] [float] NOT NULL ,
[_LEGAL_FORM] [varchar] (70) NOT NULL ,
[_FOUNDATION_DAY] [smallint] NOT NULL ,
[_FOUNDATION_MONTH] [smallint] NOT NULL ,
[_FOUNDATION_YEAR] [smallint] NOT NULL ,
[_FOUNDATION_DATE] [datetime] NOT NULL ,
[_CFS] [char] (1) NOT NULL ,
[_FLAT_FEE] [char] (1) NOT NULL ,
[_NUMBER_OF_STAFF] [varchar] (10) NOT NULL ,
[_ESTIMATED_TOTAL_ASS] [float] NOT NULL ,
[_EST_TOTAL_ASSETS_CCY] [varchar] (3) NOT NULL ,
[_MININMAL_INVESTMENT] [int] NOT NULL ,
[_INVESTMENT_CCY] [varchar] (3) NOT NULL ,
[_SHAREHOLDER1] [varchar] (51) NOT NULL ,
[_SHARE1_AMOUNT] [int] NOT NULL ,
[_SHARE1_CCY_PERCENT] [varchar] (3) NOT NULL ,
[_SHAREHOLDER2] [varchar] (51) NOT NULL ,
[_SHARE2_AMOUNT] [int] NOT NULL ,
[_SHARE2_CCY_PERCENT] [varchar] (3) NOT NULL ,
[_SHAREHOLDER3] [varchar] (51) NOT NULL ,
[_SHARE3_AMOUNT] [int] NOT NULL ,
[_SHARE3_CCY_PERCENT] [varchar] (3) NOT NULL ,
[_SHAREHOLDER4] [varchar] (51) NOT NULL ,
[_SHARE4_AMOUNT] [int] NOT NULL ,
[_SHARE4_CCY_PERCENT] [varchar] (3) NOT NULL ,
[_SHAREHOLDER5] [varchar] (51) NOT NULL ,
[_SHARE5_AMOUNT] [int] NOT NULL ,
[_SHARE5_CCY_PERCENT] [varchar] (3) NOT NULL ,
[_TELEDATA_NO] [varchar] (10) NOT NULL ,
[_TELEDATA_COMMON] [varchar] (80) NOT NULL ,
[_COUNT_CONTACT] [int] NOT NULL ,
[_COUNT_CONTLOG] [int] NOT NULL ,
[_COUNT_CLOGAAT] [int] NOT NULL ,
[_COUNT_TODO] [int] NOT NULL ,
[_COUNT_CTNTRACT] [int] NOT NULL ,
[_COUNT_TA] [int] NOT NULL ,
[_COUNT_IMPROD] [int] NOT NULL ,
[_COUNT_SERVICE] [int] NOT NULL ,
[_COUNT_BANKREL] [int] NOT NULL ,
[_COUNT_ADDCODE] [int] NOT NULL ,
[_COUNT_CNTCTPLN] [int] NOT NULL ,
[_COUNT_RAS] [int] NOT NULL ,
[_SIGNATURE_PERSON] [varchar] (8) NOT NULL ,
[_SIGN_PERSON_TITLE] [varchar] (15) NOT NULL ,
[_SIGN_PERSON_FNAME] [varchar] (25) NOT NULL ,
[_SIGN_PERSON_LNAME] [varchar] (25) NOT NULL ,
[_SIGN_PERSON_PHONE] [varchar] (20) NOT NULL ,
[_SIGN_PERSON_FAX] [varchar] (20) NOT NULL ,
[_SIGN_PERSON_OU] [varchar] (40) NOT NULL ,
[_SIGN_PERSON_DESK] [varchar] (40) NOT NULL ,
[_SIGN_PERSON_KZ] [varchar] (6) NOT NULL ,
[_SIGN_PERSON_MKZ] [varchar] (3) NOT NULL ,
[_SIGN_PERSON_COMPANY] [varchar] (30) NOT NULL ,
[_SIGN_PERSON_STREET] [varchar] (35) NOT NULL ,
[_SIGN_PERSON_ZIP] [varchar] (10) NOT NULL ,
[_SIGN_PERSON_CITY] [varchar] (30) NOT NULL ,
[_SIGN_PERSON_EMAIL] [varchar] (60) NOT NULL ,
[_SIGN_PERSON_BUSSECT] [varchar] (30) NOT NULL ,
[_SIGN_PERSON_POSITION] [varchar] (30) NOT NULL ,
[_SIGN_PERSON_TEAM] [varchar] (40) NOT NULL ,
[_SIGNATURE_DH] [varchar] (8) NOT NULL ,
[_SIGN_DH_TITLE] [varchar] (15) NOT NULL ,
[_SIGN_DH_FNAME] [varchar] (25) NOT NULL ,
[_SIGN_DH_LNAME] [varchar] (25) NOT NULL ,
[_SIGN_DH_PHONE] [varchar] (20) NOT NULL ,
[_SIGN_DH_FAX] [varchar] (20) NOT NULL ,
[_SIGN_DH_OU] [varchar] (40) NOT NULL ,
[_SIGN_DH_DESK] [varchar] (40) NOT NULL ,
[_SIGN_DH_KZ] [varchar] (6) NOT NULL ,
[_SIGN_DH_MKZ] [varchar] (3) NOT NULL ,
[_SIGN_DH_COMPANY] [varchar] (30) NOT NULL ,
[_SIGN_DH_STREET] [varchar] (35) NOT NULL ,
[_SIGN_DH_ZIP] [varchar] (10) NOT NULL ,
[_SIGN_DH_CITY] [varchar] (30) NOT NULL ,
[_SIGN_DH_EMAIL] [varchar] (60) NOT NULL ,
[_SIGN_DH_BUSSECT] [varchar] (30) NOT NULL ,
[_SIGN_DH_POSITION] [varchar] (30) NOT NULL ,
[_SIGN_DH_TEAM] [varchar] (40) NOT NULL ,
[_FIRST_NAME_ADD_USER] [varchar] (80) NOT NULL ,
[_GIVEN_NAME_ADD_USER] [varchar] (80) NOT NULL ,
[_USER_ROLE_CLASS] [varchar] (8) NOT NULL ,
[_DESK_CODE] [varchar] (8) NOT NULL ,
[_DESK_DESCRIPTION] [varchar] (20) NOT NULL ,
[_LOCATION_ADD_USER] [varchar] (30) NOT NULL ,
[_LANGUAGE_ADD_USER] [varchar] (5) NOT NULL ,
[_COMPANY_ADD_USER] [varchar] (80) NOT NULL ,
[_STREET_ADD_USER] [varchar] (80) NOT NULL ,
[_ZIP_ADD_USER] [varchar] (10) NOT NULL ,
[_TOWN_ADD_USER] [varchar] (80) NOT NULL ,
[_PHONE#_ADD_USER] [varchar] (80) NOT NULL ,
[_EMAIL_ADD_USER] [varchar] (80) NOT NULL ,
[_TITLE_ADD_USER] [varchar] (30) NOT NULL ,
[_MAIN_BANK_COUNT] [smallint] NOT NULL ,
[_VAT] [varchar] (6) NOT NULL ,
[_RMP_ID] [varchar] (9) NOT NULL

CREATE TABLE [dbo].[BANKREL] (
[_INTERNID] [int] NOT NULL ,
[_CHGDATE] [smallint] NOT NULL ,
[_CHGTIME] [smallint] NOT NULL ,
[_STAMP2] [binary] (8) NOT NULL ,
[_ID] [varchar] (9) NOT NULL ,
[_BANK] [varchar] (40) NOT NULL ,
[_MAIN_BANK] [char] (1) NOT NULL ,
[_MAIN_BANK_H] [char] (1) NOT NULL ,
[_LOCATION] [varchar] (20) NOT NULL ,
[_CONTACT_PERSON] [varchar] (30) NOT NULL ,
[_INDICATION] [float] NOT NULL ,
[_ASSETS] [float] NOT NULL ,
[_ACTIVE] [char] (1) NOT NULL ,
[_CREATED_BY] [varchar] (8) NOT NULL ,
[_CREATE_DATE] [smalldatetime] NOT NULL ,
[_CHANGED_BY] [varchar] (8) NOT NULL ,
[_CHANGE_DATE] [smalldatetime] NOT NULL ,
[_SITEID] [varchar] (9) NOT NULL

-------------------------------------------------------

Sample data in Table bankrel

_internid _id _bank _assets _change_date _siteid
----------- --------- --------------------------------------------1 B5AQ51KXS Théme 0.0 2004-08-19 00:00:00 B5AQ51POE
1 B5AQ51L3H ABN-AMRO 12.0 2004-08-19 00:00:00 B5AQ51POE
1 B5AQ51L59 AIG Privatbank 30.0 2004-08-19 00:00:00 B5AQ51POE
1 B5AQ51L83 AVIP 17.0 2004-08-19 00:00:00 B5AQ51POE
1 B5AQ51LBK Aargauische Kantonalbank 3.0 2004-08-19 00:00:00 B5AQ51POE
2 B5AQ523HH Bank de Genève 1.0 2004-08-19 00:00:00 B5AQ51RDD
2 B5AQ523KO Aargauis Kantonalb 90.0 2004-08-19 00:00:00 B5AQ51RDD
2 B5AQ523OL ABN 33.0 2004-08-19 00:00:00 B5AQ51RDD
3 B5AR454SL AIG Privatbank 34.0 2004-08-19 00:00:00 B5AQ51THS
3 B5AR455K3 Sarasin 99.0 2004-08-19 00:00:00 B5AQ51THS

--------

RMP Table should like this:
RMP Columns: _InternId, _RMP_ID, _sow_bank1, _sow_money1, _sow_date1, _sow_bank2, _sow_money2, _sow_date2, _sow_bank3,......
1 B5AQ51ITR, AIG Privatbank, 30.0, 2004-08-19 00:00:00, AVIP, 17.0, .....
2 B5AQ51R1V, Aargauische Kantonalbank, 90.0, 2004-08-19 00:00:00, ABN-AMRO, 33.0,Bank de Genève, 1.0, NULL,
3 B5AQ51T8X, Sarasin, 99.0, 2004-08-19 00:00:00, AIG Privatbank, 34.0,..,
-----

nans04
Starting Member

20 Posts

Posted - 2004-08-20 : 14:44:53

Thanks tara for your help!
Coding for this complex for me!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-20 : 14:57:08

Perhaps you can format this so that you only show which columns are important and also format the data so that it is readable. Typically, you will get a faster answer if you post the data in the form of INSERT INTO statements so that we can try it out on our machines.

Tara

nans04
Starting Member

20 Posts

Posted - 2004-08-20 : 16:35:17

Important columns & Sample data for Bankrel:
_internid, _bank, _assets, _ change_date,
1 ABN bank 21.0 2004-08-19 00:00:00
1 Privatbank 30.0 2004-08-19 00:00:00
1 AVIP 17.0 2004-08-19 00:00:00
1 Kantonalbank 90.0 2004-08-19 00:00:00
2 Bank de Genève 1.0 2004-08-19 00:00:00

Important Columns of RMP for our query:
_internid, _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3, _sow_date3, _sow_bank4, sow_money4, _sow_date4,_sow_bank5, sow_money5, _sow_date5

Typically RMP rows looks like this (after update): for above column names:
1, Kantonalbank, 90.0, 2004-08-19 00:00:00, Privatbank, 30.0, 2004-08-19 00:00:00, ABN Bank, 21.0, 2004-08-19 00:00:00, AVIP, 17.0, 2004-08-19 00:00:00
2, Bank de Genève, 1.0, 2004-08-19 00:00:00, blank, blank, blank , , , , ,

Sorry I dont have any insert into statements. I have tried by best to explain the problem I have on hand. I need to write a trigger every time when there is an insert, update on table bankrel and sort the banks by assets, update actual info (insert into) in RMP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-20 : 16:40:01

You need to write the INSERT INTO statements. That's how we come up with a solution for you. In order for us to help you quickly, we need the information in a certain format.

Tara

nans04
Starting Member

20 Posts

Posted - 2004-08-20 : 17:39:54

Sorry I am newbie to T-SQL. If I understood you correctly, the insert statement should like:
INSERT INTO RMP (_sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3, _sow_date3, _sow_bank4, sow_money4, _sow_date4,_sow_bank5, sow_money5, _sow_date5) values (select top 4 _bank, _assets, _change_date where rmp._internid = bankrel._internid and bankrel._active = 'y' order by bankrel._assets desc)???

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-20 : 17:47:18

Did you design the structure - if so I would redesign before you go any further, you might find it will solve your problems.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nans04
Starting Member

20 Posts

Posted - 2004-08-20 : 18:05:37

NO I didnt design the structure. Its an existing DB structure. I need to code for a requirement

nans04
Starting Member

20 Posts

Posted - 2004-08-22 : 06:59:24

I want to write trigger code to update values in table1 to table2.
I guess ALTER, CREATE, DROP cannot be used in a trigger.

As you can see, code must select only active('Y') names in table1 arrange in descending order of asset for each ID and updates(or insert into?)table2.
Please help me with code

Tabel1
ID, Name, Asset, Date, Active
---------------------------------
1, A, 10 , 01/08/04 Y
1, B, 16 , 06/08/04 Y
1, C, 12 , 07/08/04 Y
1, D, 13 , 10/08/04 Y
2, E, 10 , 15/08/04 Y
2, F, 11 , 16/08/04 Y
2, Y, 12 , 01/08/04 N
2, G, 15 , 17/08/04 Y
3, H, 13 , 19/08/04 Y
3, I, 15 , 02/08/04 N

Table2 after update
ID, Name1, Asset1, Date1, Name2, Asset2, Date2, Name3, Asset3, Date3, Name4, Asset4, Date4
---------------------------------
1,B,16,06/08/04,D,13,10/08/04,C,12,07/08/04,A,10,01/08/04
2,F,11,16/08/04,E,10,15/08/04,blank,blank, blank..
3,H,13,19/08/04,blank, blank...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-22 : 07:33:47

create trigger t on Table1 for insert, update, delete
as
select id, num = convert(int, 0)
into #a
from
(select id
union
select id
) a

update #a
set num = count(*)
from Table1 t1
where #a.id = t1.id
and t1.Active = 'Y'

update Table2
set Name1 = blank ,
Asset1 = blank ,
Date1 = blank ,
Name2 = blank ,
...
from table2
join #a
on Table2.id = #a.id

insert Table2
select #a.id, blank, blank, ...
from #a
left outer join Table2 t2
on t2.id = #a.id
where t2.id is null

update Table2
set Name1 = (select Name from (select top1 * from (select top 1 * from Table1 t1 where t1.id = t2.id and t1.Active = 'Y' order by Asset) a order by Asset desc) ,
Asset1 = (select Asset from (select top1 * from (select top 1 * from Table1 t1 where t1.id = t2.id and t1.Active = 'Y' order by Asset) a order by Asset desc) ,
Date1 = (select Date from (select top1 * from (select top 1 * from Table1 t1 where t1.id = t2.id and t1.Active = 'Y' order by Asset) a order by Asset desc)
from Table2 t2
join #a
on t2.id = #a.id
where #a.num >= 1

update Table2
set Name2 = (select Name from (select top2 * from (select top 1 * from Table1 t1 where t1.id = t2.id and t1.Active = 'Y' order by Asset) a order by Asset desc) ,
Asset2 = (select Asset from (select top2 * from (select top 1 * from Table1 t1 where t1.id = t2.id and t1.Active = 'Y' order by Asset) a order by Asset desc) ,
Date2 = (select Date from (select top2 * from (select top 1 * from Table1 t1 where t1.id = t2.id and t1.Active = 'Y' order by Asset) a order by Asset desc)
from Table2 t2
join #a
on t2.id = #a.id
where #a.num >= 1

........

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nans04
Starting Member

20 Posts

Posted - 2004-08-22 : 13:59:52

Thanks a lot.
The column in table2 doesnt allow null values. how do I solve this problem?
Does null value = blank??

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-22 : 14:22:11

Where I have blank put in whatever value you need - probably '' or 0.
Nothing else should try to update with null.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nans04
Starting Member

20 Posts

Posted - 2004-08-23 : 04:05:01

Hi nr,

When I run this code in QA, I these errors:

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ','.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'from'.
---------------------
update rmp
set _sow_bank3 = (select _bank from (select top 1 * from (select top 1 * from bankrel b where b._internid = r._internid and b._active = 'Y' order by b._assets) a order by _assets desc)
, _sow_money3 = (select _asset from (select top 1 * from (select top 1 * from bankrel b where b._internid = r._internid and b._active = 'Y' order by b._assets) a order by _assets desc)
, _sow_date3 = (select _change_date from (select top 1 * from (select top 1 * from bankrel b where b._internid = r._internid and b._active = 'Y' order by b._assets) a order by _assets desc)
from rmp r join #a on r._internid = #a._id where #a.num >= 1
---------------------------------

Any idea?

nans04
Starting Member

20 Posts

Posted - 2004-08-23 : 04:07:14

Hi nr,
------------------
update #a set num = count(*)from Bankrel b where #a._id = b._internid and b._active = 'Y'
------------------------------
Erro msg for this:
An aggregate may not appear in the set list of an UPDATE statement.

nans04
Starting Member

20 Posts

Posted - 2004-08-23 : 05:16:47

select _id, num = convert(int, 0) into #a from (select _id union select _id ) a;

I get this error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name '_id'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name '_id'.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-23 : 05:29:50

oops - wasn't doing very well
Don't now what the idea behind that union was - it'll probably turn up later.

select distinct id, num = convert(int, 0)
into #a
from
Tabel1
where Active = 'Y'

update #a
set num = (select count(*)
from Table1 t1
where #a.id = t1.id
and t1.Active = 'Y')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nans04
Starting Member

20 Posts

Posted - 2004-08-23 : 06:23:37

Hi, Thanks for your earlier reply. I am not able to get rid of syntax errors.....

----
update Table2
set Name1 = (select Name from (select top 1 * from (select top 1 * from Table1 t1 where t1.id = t2.id and t1.Active = 'Y' order by Asset) a order by Asset desc) ,
Asset1 = (select Asset from (select top 1 * from (select top 1 * from Table1 t1 where t1.id = t2.id and t1.Active = 'Y' order by Asset) a order by Asset desc) ,
Date1 = (select Date from (select top 1 * from (select top 1 * from Table1 t1 where t1.id = t2.id and t1.Active = 'Y' order by Asset) a order by Asset desc)
from Table2 t2
join #a
on t2.id = #a.id
where #a.num >= 1

-----

I get these errors:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ','.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'from'.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-23 : 06:39:52

try
update Table2
set Name1 = (select Name from (select top 1 * from (select top 1 * from Table1 t1 where t1.id = t2.id and t1.Active = 'Y' order by Asset) a order by Asset desc) b) ,
Asset1 = (select Asset from (select top 1 * from (select top 1 * from Table1 t1 where t1.id = t2.id and t1.Active = 'Y' order by Asset) a order by Asset desc) b) ,
Date1 = (select Date from (select top 1 * from (select top 1 * from Table1 t1 where t1.id = t2.id and t1.Active = 'Y' order by Asset) a order by Asset desc) b)
from Table2 t2
join #a
on t2.id = #a.id
where #a.num >= 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nans04
Starting Member

20 Posts

Posted - 2004-08-23 : 07:01:59

Thanks again nr!
NO syntax error this time. but when I run query, I get these messages:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 't2' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 't2' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 't2' does not match with a table name or alias name used in the query.

May be I have use kie this:
...select top 1 * from Table1 t1, Table2 t2 where t1.id = t2.id and t1.Active = 'Y' .... ????

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources