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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy