January 17, 2019 at 9:37 am
I have insert trigger for inserting records from T_PDF table into T_WV_RESULT Table. The below trigger is fired when new records are inserted into the T_PDF table. Now need update trigger for the below
T_PDF table structure(Source Table)
CREATE TABLE [dbo].[T_PDF](
[F_PRODUCT] [varchar](50) NOT NULL,
[F_LANGUAGE] [varchar](2) NOT NULL,
[F_FORMAT] [varchar](3) NOT NULL,
[F_SUBFORMAT] [varchar](4) NOT NULL,
[F_DATE_STAMP] [datetime] NOT NULL,
[F_PLANT] [varchar](10) NOT NULL,
[F_SUPPLIER] [varchar](200) NOT NULL,
[F_PRODUCT_NAME] [nvarchar](2000) NULL,
[F_DATE_REVISED] [datetime] NULL,
[F_PDF] [varbinary](max) NULL,
[F_AUTHORIZED] [smallint] NULL,
[F_Published_Date] [datetime] NULL,
[F_CAS_NUMBERS] [varchar](4000) NULL,
[F_COMPONENT_IDS] [varchar](4000) NULL,
[F_ISSUE_DATE] [datetime] NULL,
[F_DISPOSAL_DATE] [datetime] NULL,
[F_DOC_TYPE] [smallint] NOT NULL,
[F_DOC_PATH] [varchar](200) NULL,
[F_KEYWORDS] [varchar](255) NULL,
[F_CUSTOM1] [nvarchar](4000) NULL,
[F_CUSTOM2] [nvarchar](4000) NULL,
[F_CUSTOM3] [nvarchar](4000) NULL,
[F_CUSTOM4] [nvarchar](4000) NULL,
[F_CUSTOM5] [nvarchar](4000) NULL,
[F_GUID] [uniqueidentifier] NULL,
[F_User_Updated] [varchar](15) NULL,
[F_REV_NUM] [real] NULL,
[F_IS_S3] [bit] NULL,
[F_COUNTER] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_T_PDF] PRIMARY KEY CLUSTERED
(
[F_PRODUCT] ASC,
[F_LANGUAGE] ASC,
[F_FORMAT] ASC,
[F_SUBFORMAT] ASC,
[F_DATE_STAMP] ASC,
[F_PLANT] ASC,
[F_DOC_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
[T_WV_RESULT] Target Table
CREATE TABLE [dbo].[T_WV_RESULT](
[ID] [varchar](50) NULL,
[NAME] [nvarchar](2000) NULL,
[FMTCODE] [varchar](3) NULL,
[SFMTCODE] [varchar](4) NULL,
[SFMT] [varchar](30) NULL,
[LANGCODE] [varchar](2) NULL,
[LANG] [varchar](50) NULL,
[PLANTCODE] [varchar](10) NULL,
[RDATE] [datetime] NULL,
[CASNUM] [varchar](4000) NULL,
[TN] [varchar](4000) NULL,
[CP] [varchar](4000) NULL,
[CUS5] [varchar](4000) NULL,
[MANU] [varchar](max) NULL,
[SYN] [nvarchar](4000) NULL,
[ICO] [nvarchar](4000) NULL,
[DOC] [varchar](2000) NULL,
[COVER] [varchar](100) NULL,
[GUID] [varchar](256) NULL,
[SDS] [varchar](4) NULL,
[SITECODE] [varchar](8) NULL,
[PDATE] [datetime] NULL,
[CASDATA] [varchar](4000) NULL,
[CUS3] [varchar](4000) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Insert TriggerCREATE TRIGGER [dbo].[T_PDF_WV_RESULT]
ON [dbo].[T_PDF]
AFTER INSERT
AS
BEGIN
INSERT INTO T_WV_RESULT (ID , NAME, SFMTCODE, SFMT,
LANGCODE, LANG, PLANTCODE, FMTCODE,
RDATE, CASNUM, TN, CP,
CUS5, MANU, SYN, ICO,
DOC, COVER, [GUID], SDS,
SITECODE, PDATE, CASDATA, CUS3)
SELECT
TP.F_PRODUCT,
TP.F_PRODUCT_NAME,
TP.F_SUBFORMAT,
(SELECT MS.F_MSDSTYPE_DESC FROM T_MSDSTYPES MS
WHERE MS.F_MSDSTYPE = TP.F_SUBFORMAT AND MS.F_FORMAT = TP.F_FORMAT) AS SFMT,
TP.F_LANGUAGE,
(SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL
WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) AS LANG,
TP.F_PLANT AS PLANTCODE,
TP.F_FORMAT AS FMTCODE,
TP.F_DATE_REVISED,
F_CAS_NUMBERS,
TP.F_CUSTOM1,
TP.F_CUSTOM2,
(SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC
WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
(SELECT F_PHRASE FROM [V_PROD_ALIAS_MANU]
WHERE F_PRODUCT = TP.F_PRODUCT) AS MANU,
(SELECT F_DATA FROM V_PROD_ALIAS_SYN
WHERE F_PRODUCT = TP.F_PRODUCT) AS SYN,
(SELECT F_DATA FROM V_PROD_ALIAS_ICO
WHERE F_PRODUCT = TP.F_PRODUCT) AS ICO,
'SDS - ' + (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL
WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC,
'' AS COVER, CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID], 'PDF' AS SDS,
(SELECT TOP 1 F_TEXT_CODE FROM V_PROD_ALIAS_SITE
WHERE F_PRODUCT = TP.F_PRODUCT) AS SITECODE,
TP.F_PUBLISHED_DATE,
(SELECT F_DATA FROM V_PROD_ALIAS_CASN
WHERE F_PRODUCT = TP.F_PRODUCT) AS CASDATA, TP.F_CUSTOM3 AS CUS3
FROM
INSERTED TP
WHERE
TP.F_AUTHORIZED IN (0,3)
How can i write update trigger for the above one?It contains some sub queries.if i made any changes it reflects in T_WV_Result table.
January 17, 2019 at 10:49 am
why are you doing all the subqueries instead of joining to the tables themselves? Every row is running 9 select statements to get data. What is the purpose of the second table?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 17, 2019 at 12:21 pm
What is the purpose of creating this table? Wouldn't it be easier to create a view and use that instead of duplicating data?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 20, 2019 at 3:46 am
The main table T_pdf contains millions of records,so we created another one table and moved some required records into another table(T_WV_RESULT).View will be faster then table? because view does not store records so we created another table and also above query
contains five views (V_PROD_ALIAS_MANU,V_PROD_ALIAS_SYN,V_PROD_ALIAS_ICO V_PROD_ALIAS_SITE,V_PROD_ALIAS_CASN)
so please suggest
January 20, 2019 at 9:56 am
view will be faster then table?.Please suggest.so that i can avoid second table.
January 20, 2019 at 10:12 am
jkramprakash - Sunday, January 20, 2019 9:56 AMview will be faster then table?.Please suggest.so that i can avoid second table.
If you have the right indexes in place - and the code is optimized - then a second table would not be necessary. Without further information on the queries that are slow - we really cannot determine what would be the better approach. You could look at filtered indexes or indexed views but that is only a guess.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 21, 2019 at 11:05 pm
jkramprakash - Sunday, January 20, 2019 9:56 AMview will be faster then table?.Please suggest.so that i can avoid second table.
No. It's not faster than a table. It's just another way of looking at a table. That's why it's call a "View".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply