Solved

how to create SQL trigger using IF condition as well doing inner joins

Posted on 2013-01-14
41
376 Views
Last Modified: 2013-01-17
I have below sample format table structure:

CUSTOMER TABLE
-----------------
CUSTID INT,//PRIMARY KEY
CUSTDATA VARCHAR

EMPLOYEE TABLE
--------------------
EMPID INT,//PRIMARY KEY
EMPDATA VARCHAR

ITEM TABLE
---------------
ITEMID INT,//PRIMARY KEY
CUSTID INT,//REFERENCE KEY
ITEMTYPE INT //1 FOR CUSTOMER AND 2 FOR EMPLOYEE
EMPID INT//REFERENCE KEY

MERGEDATA TABLE
-----------------
MERGEID INT,
CUSTDATA VARCHAR,
EMPDATA VARCHAR
ACTION VARCHAR

Open in new window


Above I have four tables (CUSTOMER, EMPLOYEE, ITEM and MERGEDATA),

now I want to create SQL TRIGGER on ITEM table for INSERT/UPDATE/DELETE events, so whenever activity is done on ITEM table it populate my MERGEDATA table with the action done on ITEM table

so if any new records gets inserted in ITEM table a new entry goes in MERGEDATA table depending on the CUSTOMER or EMPLOYEE entries, like if there is any entry from customer it will add one entry in ITEM table as well as in CUSTOMER table as below

CUSTOMER TABLE
----------------
123, TESTCUSTOMER

ITEM TABLE
---------------
1, 123, 1, 0
And same goes for Employee table too, however in ITEM table record will be as 1, 456, 0, 2.

Open in new window


Now I want to write if condition in Trigger that if there is entry from CUSTOMER table IF (ITEMTYPE == 1) //Entry in MERGEDATA will as below

MERGEDATA TABLE
-----------------
1, 123,0,ACTION (will depend on type of trigger event trigger if INSERT(ACTION = INS), for update (UPDATE = UPD) etc

Open in new window


so in short I am looking below things in trigger:

How to write IF condition in trigger
How to do inner joins from other table in trigger
Please suggest!!
0
Comment
Question by:tia_kamakshi
  • 23
  • 18
41 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777146
createYou need to  twoseparate triggers - one for each operation Insert , update  as you need separate action on each


If my understaning is correct triggers looks like thuis
For Merge tbale I cam condisdering MergeID is autoincrement primary key so need to insert it
I don't think you need if conditon, purpose will serve with CASE consition only. see below

CREATE TRIGGER Item_Insert
ON dbo.Item FOR INSERT
AS
   INSERT INTO dbo.MergeTable(CUSTDATA, EMPDATA, ACTION)
      SELECT
Case When I.ItemType= 1 then C.CUSTDATA else 0 end ,
Case When I.ItemType= 2 then E.EMPDATA else 0 end ,
'INS'
         
      FROM Inserted I
Left join Customer C on I.CustId = C.CustId
Left Join Employee E on I.EMPID = E.EMPID



CREATE TRIGGER Item_Update
ON dbo.Item FOR INSERT
AS
   INSERT INTO dbo.MergeTable(CUSTDATA, EMPDATA, ACTION)
      SELECT
Case When I.ItemType= 1 then C.CUSTDATA else 0 end ,
Case When I.ItemType= 2 then E.EMPDATA else 0 end ,
'UPD'
         
      FROM Inserted I
Left join Customer C on I.CustId = C.CustId
Left Join Employee E on I.EMPID = E.EMPID
0
 

Author Comment

by:tia_kamakshi
ID: 38777176
Hi Pratima,

Sorry but I have given sample table structure  above, now below is the actual structure for my tables and I tried to implement above logic, I am getting lots of multiple entries in my populating tabel i.e. mergedata

I have FOUR tables as below:

Main Table ITEMS:
CREATE TABLE [dbo].[ITEMS](
	[ITEM_REFERENCE_ID] [int] NOT NULL,
	[PUBLICATION_ID] [int] NOT NULL,
	[MAJOR_VERSION] [int] NULL,
	[MINOR_VERSION] [int] NULL,
	[OWNING_PUBLICATION_ID] [int] NULL,
	[ITEM_TYPE] [int] NOT NULL,
	[TITLE] [nvarchar](255) NOT NULL,
	[CREATION_DATE] [datetime] NULL,
	[INITIAL_PUBLICATION_DATE] [datetime] NULL,
	[LAST_PUBLISHED_DATE] [datetime] NULL,
	[TRUSTEE] [nvarchar](255) NOT NULL,
	[MODIFICATION_DATE] [datetime] NULL,
	[ITEM_SELECTOR] [nvarchar](50) NULL,
 CONSTRAINT [PK_ITEMS] PRIMARY KEY CLUSTERED 
(
	[ITEM_REFERENCE_ID] ASC,
	[PUBLICATION_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


Components:

CREATE TABLE [dbo].[COMPONENT](
	[ITEM_REFERENCE_ID] [int] NOT NULL,
	[PUBLICATION_ID] [int] NOT NULL,
	[SCHEMA_ID] [int] NOT NULL,
	[IS_MULTIMEDIA] [int] NOT NULL,
 CONSTRAINT [PK_COMPONENT] PRIMARY KEY CLUSTERED 
(
	[ITEM_REFERENCE_ID] ASC,
	[PUBLICATION_ID] 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]

Open in new window


PAGE:

CREATE TABLE [dbo].[PAGE](
	[ITEM_REFERENCE_ID] [int] NOT NULL,
	[PUBLICATION_ID] [int] NOT NULL,
	[FILENAME] [nvarchar](255) NULL,
	[URL] [nvarchar](255) NOT NULL,
	[TEMPLATE_ID] [int] NOT NULL,
 CONSTRAINT [PK_PAGE] PRIMARY KEY CLUSTERED 
(
	[ITEM_REFERENCE_ID] ASC,
	[PUBLICATION_ID] 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]

Open in new window



TABLE TO BE POPULATED AUTN_ITEMS:

CREATE TABLE [dbo].[AUTN_ITEMS](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ACTION] [nvarchar](3) NOT NULL,
	[PUBLICATION_ID] [int] NOT NULL,
	[ITEM_REFERENCE_ID] [int] NOT NULL,
	[ITEM_TYPE] [int] NOT NULL,
	[LAST_PUBLISHED_DATE] [datetime] NOT NULL,
	[URL] [nvarchar](255) NULL,
	[SCHEMA_ID] [int] NULL,
	[flag] [nvarchar](1) NULL,
 CONSTRAINT [PK_AUTN_ITEMS] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


Now in above AUTN_ITEMS table, I want values to be populated from ITEMS tables.
So if ITEM_TYPE = 16 then URL will be NULL, However SCHEMA_ID will be taken from COMPOENT Table
So if ITEM_TYPE = 64 then URL will be taken from PAGE, However SCHEMA_ID will be NULL

Please suggest!!
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777244
try this

 
   INSERT INTO dbo.AUTN_ITEMS(ACTION, PUBLICATION_ID,ITEM_REFERENCE_Id ,
ITEM_TYPE,URL,SCHEMA_ID)
      SELECT
'INS',I.PUBLICATION_ID,I.ITEM_REFERENCE_Id , I.ITEM_TYPE
Case When I.Item_Type= 16 then NULL
 When I,ITEM_TYPE=12 then P.URL ELSE NULL end ,
Case When I.Item_Type= 16 then C.SCHEMA_ID
when I,ITEM_TYPE=12 then NULL
else NULL end
         
      FROM Inserted I
Left join COMPONENT C on I.ITEM_REFERENCE_Id = C.ITEM_REFERENCE_Id and I.PUBLICATION_ID = C.PUBLICATION_ID
Left Join PAGE P on I.ITEM_REFERENCE_Id = P.ITEM_REFERENCE_Id and I.PUBLICATION_ID = P.PUBLICATION_ID
0
 

Author Comment

by:tia_kamakshi
ID: 38777336
Hi Pratima,

Thanks!!.

I am getting extra NULL value inserted my table AUTN_ITEMS for both ITEM_TYPE (64 and 16),

Can you please suggest what extra check we can put on the conditions.

Thanks.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777357
Can you please provide me some data from this tables so it is easy to unnderstand what's going wrong ?
0
 

Author Comment

by:tia_kamakshi
ID: 38777378
Below are the data taken from Table:

ITEMS:
ITEM_REFERENCE_ID	PUBLICATION_ID	MAJOR_VERSION	MINOR_VERSION	OWNING_PUBLICATION_ID	ITEM_TYPE	TITLE	CREATION_DATE	INITIAL_PUBLICATION_DATE	LAST_PUBLISHED_DATE	TRUSTEE	MODIFICATION_DATE	ITEM_SELECTOR
342345	233	2	0	229	16	XXX Booking Disabled	2008-06-05 15:34:50.000	2013-01-15 12:54:10.177	2013-01-15 12:55:56.413	dxbmiwwtdn2\nargisse	2008-06-05 15:34:50.000	component
342349	233	3	0	232	64	Internet Booking Disabled	2008-06-05 15:39:05.000	2013-01-15 12:54:09.913	2013-01-15 12:55:56.163		2008-06-05 15:39:29.000	page

Open in new window


COMPONENT:
ITEM_REFERENCE_ID	PUBLICATION_ID	SCHEMA_ID	IS_MULTIMEDIA
342345	233	190439	0

Open in new window


PAGE:
ITEM_REFERENCE_ID	PUBLICATION_ID	FILENAME	URL	TEMPLATE_ID
342349	233	/global/plan_book/ibe_unavailable.aspx	/plan_book/ibe_unavailable.aspx	190644

Open in new window


AUTN_TABLE:
ID	ACTION	PUBLICATION_ID	ITEM_REFERENCE_ID	ITEM_TYPE	LAST_PUBLISHED_DATE	URL	SCHEMA_ID	flag
2	UPD	233	342349	64	2013-01-15 12:54:10.133	/plan_book/ibe_unavailable.aspx	NULL	NULL
3	ADD	233	342345	16	2013-01-15 12:54:10.273	NULL	NULL	NULL

Open in new window


ON DELETE it is not inserting any values for URL and SCHEMA_ID, please below:
ID	ACTION	PUBLICATION_ID	ITEM_REFERENCE_ID	ITEM_TYPE	LAST_PUBLISHED_DATE	URL	SCHEMA_ID	flag
7	DEL	233	342349	64	2013-01-15 12:55:56.163	NULL	NULL	NULL
8	DEL	233	342345	16	2013-01-15 12:55:56.413	NULL	NULL	NULL

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777389
ON DELETE ?
have you user deleted tbale instade of inserted ?

share the query you suded for delete must be something like this
INSERT INTO dbo.AUTN_ITEMS(ACTION, PUBLICATION_ID,ITEM_REFERENCE_Id ,
ITEM_TYPE,URL,SCHEMA_ID)
      SELECT
'Del',I.PUBLICATION_ID,I.ITEM_REFERENCE_Id , I.ITEM_TYPE
Case When I.Item_Type= 16 then NULL
 When I,ITEM_TYPE=12 then P.URL ELSE NULL end ,
Case When I.Item_Type= 16 then C.SCHEMA_ID
when I,ITEM_TYPE=12 then NULL
else NULL end
         
      FROM deleted I
Left join COMPONENT C on I.ITEM_REFERENCE_Id = C.ITEM_REFERENCE_Id and I.PUBLICATION_ID = C.PUBLICATION_ID
Left Join PAGE P on I.ITEM_REFERENCE_Id = P.ITEM_REFERENCE_Id and I.PUBLICATION_ID = P.PUBLICATION_ID
0
 

Author Comment

by:tia_kamakshi
ID: 38777398
Hi Pratima,

Below are the trigger code I have used to populate AUTN_ITEMS:

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'AUTN_INSERT_ITEMS' AND TYPE = 'TR')
   DROP TRIGGER AUTN_INSERT_ITEMS
GO
CREATE TRIGGER AUTN_INSERT_ITEMS ON ITEMS 
FOR INSERT
AS
BEGIN
	   INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
		SELECT 
		'ADD',
		I.PUBLICATION_ID, 
		I.ITEM_REFERENCE_ID, 
		I.ITEM_TYPE, 
		I.LAST_PUBLISHED_DATE,
		CASE WHEN I.ITEM_TYPE= 16 THEN NULL 
		WHEN I.ITEM_TYPE=64 THEN P.URL END ,
		CASE WHEN I.ITEM_TYPE= 16 THEN C.SCHEMA_ID 
		WHEN I.ITEM_TYPE=64 THEN NULL END 			
		FROM INSERTED I 
		LEFT JOIN COMPONENT C ON I.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = C.PUBLICATION_ID 
		LEFT JOIN PAGE P ON I.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = P.PUBLICATION_ID
				 
END

GO

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'AUTN_UPDATE_ITEMS' AND TYPE = 'TR')
   DROP TRIGGER AUTN_UPDATE_ITEMS
GO
CREATE TRIGGER AUTN_UPDATE_ITEMS ON ITEMS  
FOR UPDATE
AS
BEGIN
	INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
		SELECT 
		'UPD',
		I.PUBLICATION_ID, 
		I.ITEM_REFERENCE_ID, 
		I.ITEM_TYPE, 
		I.LAST_PUBLISHED_DATE,
		CASE WHEN I.ITEM_TYPE= 16 THEN NULL 
		WHEN I.ITEM_TYPE=64 THEN P.URL END ,
		CASE WHEN I.ITEM_TYPE= 16 THEN C.SCHEMA_ID 
		WHEN I.ITEM_TYPE=64 THEN NULL END 		
		FROM INSERTED I 
		LEFT JOIN COMPONENT C ON I.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = C.PUBLICATION_ID 
		LEFT JOIN PAGE P ON I.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = P.PUBLICATION_ID
END
GO
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'AUTN_DELETE_ITEMS' AND TYPE = 'TR')
   DROP TRIGGER AUTN_DELETE_ITEMS
GO
CREATE TRIGGER AUTN_DELETE_ITEMS ON ITEMS  
FOR DELETE
AS
BEGIN
	INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
		SELECT 
		'DEL',
		I.PUBLICATION_ID, 
		I.ITEM_REFERENCE_ID, 
		I.ITEM_TYPE, 
		I.LAST_PUBLISHED_DATE,
		CASE WHEN I.ITEM_TYPE= 16 THEN NULL 
		WHEN I.ITEM_TYPE=64 THEN P.URL END ,
		CASE WHEN I.ITEM_TYPE= 16 THEN C.SCHEMA_ID 
		WHEN I.ITEM_TYPE=64 THEN NULL END 		
		FROM DELETED I 
		LEFT JOIN COMPONENT C ON I.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = C.PUBLICATION_ID
		LEFT JOIN PAGE P ON I.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = P.PUBLICATION_ID		
END
GO

Open in new window


Please suggest if any changes are required!! or we can some view to get all above done
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777442
you are getting problem with only delete one right ?
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777458
I am not seeing any problem...

can you share output of this query to me

SELECT
            'DEL',
            I.PUBLICATION_ID,
            I.ITEM_REFERENCE_ID,
            I.ITEM_TYPE,
            I.LAST_PUBLISHED_DATE,
            CASE WHEN I.ITEM_TYPE= 16 THEN NULL
            WHEN I.ITEM_TYPE=64 THEN P.URL END ,
            CASE WHEN I.ITEM_TYPE= 16 THEN C.SCHEMA_ID
            WHEN I.ITEM_TYPE=64 THEN NULL END             
            FROM Item I
            LEFT JOIN COMPONENT C ON I.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = C.PUBLICATION_ID
            LEFT JOIN PAGE P ON I.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = P.PUBLICATION_ID            
                     Where I.ITEM_REFERENCE_ID in(342345,342349)
0
 

Author Comment

by:tia_kamakshi
ID: 38777462
Hi Pratima,

INSERT and DELETE both,

If above scenario does not work, please suggest using new VIEW and populating same from there.

Please suggest!!
0
 

Author Comment

by:tia_kamakshi
ID: 38777471
Hi Pratima,

Below is the result I got from above query"

(No column name)	PUBLICATION_ID	ITEM_REFERENCE_ID	ITEM_TYPE	LAST_PUBLISHED_DATE	(No column name)	(No column name)
DEL	285	342345	16	2010-07-14 03:21:50.733	NULL	190439
DEL	309	342345	16	2008-09-17 12:01:14.140	NULL	190439
DEL	365	342345	16	2008-06-19 01:27:27.813	NULL	190439
DEL	372	342345	16	2009-08-03 01:49:34.517	NULL	190439
DEL	373	342345	16	2008-06-19 01:06:14.483	NULL	190439
DEL	374	342345	16	2008-06-19 01:03:00.877	NULL	190439
DEL	386	342345	16	2008-07-06 06:49:44.953	NULL	190439
DEL	387	342345	16	2008-06-19 01:00:55.030	NULL	190439
DEL	388	342345	16	2008-06-19 01:21:05.377	NULL	190439
DEL	389	342345	16	2008-06-19 01:15:05.890	NULL	190439
DEL	390	342345	16	2010-09-05 20:00:36.187	NULL	190439
DEL	391	342345	16	2008-06-19 01:05:26.657	NULL	190439
DEL	392	342345	16	2008-06-19 01:05:02.813	NULL	190439
DEL	393	342345	16	2008-06-19 01:02:04.967	NULL	190439
DEL	394	342345	16	2008-06-19 01:02:27.967	NULL	190439
DEL	403	342345	16	2008-11-17 15:00:45.890	NULL	190439
DEL	404	342345	16	2008-11-18 14:40:18.687	NULL	190439
DEL	410	342345	16	2009-05-16 22:01:33.627	NULL	190439
DEL	415	342345	16	2010-07-14 03:27:02.703	NULL	190439
DEL	416	342345	16	2010-07-14 03:35:20.467	NULL	190439
DEL	417	342345	16	2010-07-26 20:59:47.547	NULL	190439
DEL	419	342345	16	2010-07-18 01:51:37.467	NULL	190439
DEL	422	342345	16	2010-08-03 20:59:36.717	NULL	190439
DEL	428	342345	16	2009-06-12 21:24:16.500	NULL	190439
DEL	436	342345	16	2009-11-25 09:21:50.500	NULL	190439
DEL	437	342345	16	2009-12-15 23:25:45.170	NULL	190439
DEL	438	342345	16	2009-11-25 09:20:43.640	NULL	190439
DEL	439	342345	16	2009-11-25 09:28:24.500	NULL	190439
DEL	440	342345	16	2009-11-25 09:28:03.343	NULL	190439
DEL	442	342345	16	2009-11-25 09:28:46.377	NULL	190439
DEL	443	342345	16	2009-11-25 09:35:56.170	NULL	190439
DEL	444	342345	16	2009-11-25 09:35:35.157	NULL	190439
DEL	445	342345	16	2009-11-25 09:34:34.467	NULL	190439
DEL	447	342345	16	2009-10-25 09:28:42.827	NULL	190439
DEL	449	342345	16	2009-11-08 00:03:19.563	NULL	190439
DEL	453	342345	16	2009-11-25 09:36:17.063	NULL	190439
DEL	456	342345	16	2010-01-27 09:19:07.360	NULL	190439
DEL	482	342345	16	2011-01-21 04:05:19.627	NULL	190439
DEL	483	342345	16	2010-08-14 18:18:39.250	NULL	190439
DEL	484	342345	16	2010-09-06 20:00:12.453	NULL	190439
DEL	485	342345	16	2010-09-26 22:59:10.420	NULL	190439
DEL	486	342345	16	2011-01-27 14:43:06.110	NULL	190439
DEL	285	342349	64	2010-07-14 03:20:30.437	/lb/English/plan_book/ibe_unavailable.aspx	NULL
DEL	309	342349	64	2008-09-17 12:01:14.030	/sd/English/plan_book/ibe_unavailable.aspx	NULL
DEL	365	342349	64	2008-06-19 01:27:09.233	/ch/italian/plan_book/ibe_unavailable.aspx	NULL
DEL	372	342349	64	2009-08-03 01:49:06.920	/gr/greek/plan_book/ibe_unavailable.aspx	NULL
DEL	373	342349	64	2008-06-19 01:05:57.157	/tr/turkish/plan_book/ibe_unavailable.aspx	NULL
DEL	374	342349	64	2008-06-19 01:02:38.187	/hk/chinese/plan_book/ibe_unavailable.aspx	NULL
DEL	386	342349	64	2008-07-06 06:49:44.877	/ci/french/plan_book/ibe_unavailable.aspx	NULL
DEL	387	342349	64	2008-06-19 01:00:33.233	/tn/french/plan_book/ibe_unavailable.aspx	NULL
DEL	388	342349	64	2008-06-19 01:20:46.517	/tn/arabic/plan_book/ibe_unavailable.aspx	NULL
DEL	389	342349	64	2008-06-19 01:14:47.530	/ma/french/plan_book/ibe_unavailable.aspx	NULL
DEL	390	342349	64	2010-09-05 20:00:36.047	/ma/arabic/plan_book/ibe_unavailable.aspx	NULL
DEL	391	342349	64	2008-06-19 01:05:08.827	/be/english/plan_book/ibe_unavailable.aspx	NULL
DEL	392	342349	64	2008-06-19 01:04:39.233	/be/french/plan_book/ibe_unavailable.aspx	NULL
DEL	393	342349	64	2008-06-19 01:01:43.610	/pl/english/plan_book/ibe_unavailable.aspx	NULL
DEL	394	342349	64	2008-06-19 01:02:10.610	/nl/english/plan_book/ibe_unavailable.aspx	NULL
DEL	403	342349	64	2008-11-17 15:00:08.327	/jo/arabic/plan_book/ibe_unavailable.aspx	NULL
DEL	404	342349	64	2008-11-18 14:39:51.467	/kw/arabic/plan_book/ibe_unavailable.aspx	NULL
DEL	410	342349	64	2009-05-16 22:01:33.280	/ao/english/plan_book/ibe_unavailable.aspx	NULL
DEL	415	342349	64	2010-07-14 03:26:03.280	/lb/arabic/plan_book/ibe_unavailable.aspx	NULL
DEL	416	342349	64	2010-07-14 03:34:09.187	/lb/french/plan_book/ibe_unavailable.aspx	NULL
DEL	417	342349	64	2010-07-26 20:59:47.360	/ly/arabic/plan_book/ibe_unavailable.aspx	NULL
DEL	419	342349	64	2010-07-18 01:50:26.030	/qa/arabic/plan_book/ibe_unavailable.aspx	NULL
DEL	422	342349	64	2010-08-03 20:59:36.267	/ye/arabic/plan_book/ibe_unavailable.aspx	NULL
DEL	428	342349	64	2009-06-12 21:22:49.860	/ao/portuguese/plan_book/ibe_unavailable.aspx	NULL
DEL	447	342349	64	2009-10-25 09:28:13.017	/ar/english/plan_book/ibe_unavailable.aspx	NULL
DEL	449	342349	64	2009-11-08 00:02:50.000	/cl/english/plan_book/ibe_unavailable.aspx	NULL
DEL	456	342349	64	2010-01-27 09:18:17.517	/cz/english/plan_book/ibe_unavailable.aspx	NULL
DEL	483	342349	64	2010-08-14 18:17:51.327	/ca/french/plan_book/ibe_unavailable.aspx	NULL
DEL	484	342349	64	2010-09-06 20:00:12.280	/bg/english/plan_book/ibe_unavailable.aspx	NULL
DEL	485	342349	64	2010-09-26 22:59:10.297	/ro/english/plan_book/ibe_unavailable.aspx	NULL

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777472
share the output of query to me in last comment I have given
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777477
this also please

I am not seeing any problem...

can you share output of this query to me

SELECT
            'DEL',
            I.PUBLICATION_ID,
            I.ITEM_REFERENCE_ID,
            I.ITEM_TYPE,
            I.LAST_PUBLISHED_DATE,
            CASE WHEN I.ITEM_TYPE= 16 THEN NULL
            WHEN I.ITEM_TYPE=64 THEN P.URL END ,
            CASE WHEN I.ITEM_TYPE= 16 THEN C.SCHEMA_ID
            WHEN I.ITEM_TYPE=64 THEN NULL END             
            FROM Item I
            LEFT JOIN COMPONENT C ON I.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = C.PUBLICATION_ID
            LEFT JOIN PAGE P ON I.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = P.PUBLICATION_ID            
                     Where I.ITEM_REFERENCE_ID in(342345,342349) and
I.PUBLICATION_ID = 233
0
 

Author Comment

by:tia_kamakshi
ID: 38777490
Hi Pratima,

Below is the output of above query when there is no deletion, just insertion/Update is done.
(No column name)	PUBLICATION_ID	ITEM_REFERENCE_ID	ITEM_TYPE	LAST_PUBLISHED_DATE	(No column name)	(No column name)
DEL	233	342345	16	2013-01-15 14:00:12.373	NULL	190439
DEL	233	342349	64	2013-01-15 14:00:12.230	/english/plan_book/ibe_unavailable.aspx	NULL

Open in new window


If above does not work, can you suggest how to implement using VIEW or is possible using VIEW
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777495
try this .. let me know output

EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'AUTN_INSERT_ITEMS' AND TYPE = 'TR')
   DROP TRIGGER AUTN_INSERT_ITEMS
GO
CREATE TRIGGER AUTN_INSERT_ITEMS ON ITEMS
FOR INSERT
AS
BEGIN
         INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
            SELECT
            'ADD',
            I.PUBLICATION_ID,
            I.ITEM_REFERENCE_ID,
            I.ITEM_TYPE,
            I.LAST_PUBLISHED_DATE,
            CASE WHEN I.ITEM_TYPE= 16 THEN NULL
            WHEN I.ITEM_TYPE=64 THEN P.URL END ,
            CASE WHEN I.ITEM_TYPE= 16 THEN C.SCHEMA_ID
            WHEN I.ITEM_TYPE=64 THEN NULL END                   
            FROM ITEM I
Inner join Inserted INS on  I.ITEM_REFERENCE_ID = INS.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = INS.PUBLICATION_ID
            LEFT JOIN COMPONENT C ON I.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = C.PUBLICATION_ID
            LEFT JOIN PAGE P ON I.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = P.PUBLICATION_ID
                         
END

GO

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'AUTN_UPDATE_ITEMS' AND TYPE = 'TR')
   DROP TRIGGER AUTN_UPDATE_ITEMS
GO
CREATE TRIGGER AUTN_UPDATE_ITEMS ON ITEMS  
FOR UPDATE
AS
BEGIN
      INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
            SELECT
            'UPD',
            I.PUBLICATION_ID,
            I.ITEM_REFERENCE_ID,
            I.ITEM_TYPE,
            I.LAST_PUBLISHED_DATE,
            CASE WHEN I.ITEM_TYPE= 16 THEN NULL
            WHEN I.ITEM_TYPE=64 THEN P.URL END ,
            CASE WHEN I.ITEM_TYPE= 16 THEN C.SCHEMA_ID
            WHEN I.ITEM_TYPE=64 THEN NULL END             
                  FROM ITEM I
Inner join Inserted INS on  I.ITEM_REFERENCE_ID = INS.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = INS.PUBLICATION_ID
            
            LEFT JOIN COMPONENT C ON I.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = C.PUBLICATION_ID
            LEFT JOIN PAGE P ON I.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = P.PUBLICATION_ID
END
GO

GO
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'AUTN_DELETE_ITEMS' AND TYPE = 'TR')
   DROP TRIGGER AUTN_DELETE_ITEMS
GO
CREATE TRIGGER AUTN_DELETE_ITEMS ON ITEMS  
FOR DELETE
AS
BEGIN
      INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
            SELECT
            'DEL',
            I.PUBLICATION_ID,
            I.ITEM_REFERENCE_ID,
            I.ITEM_TYPE,
            I.LAST_PUBLISHED_DATE,
            CASE WHEN I.ITEM_TYPE= 16 THEN NULL
            WHEN I.ITEM_TYPE=64 THEN P.URL END ,
            CASE WHEN I.ITEM_TYPE= 16 THEN C.SCHEMA_ID
            WHEN I.ITEM_TYPE=64 THEN NULL END             
                              FROM ITEM I
Inner join deleted INS on  I.ITEM_REFERENCE_ID = INS.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = INS.PUBLICATION_ID

            LEFT JOIN COMPONENT C ON I.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = C.PUBLICATION_ID
            LEFT JOIN PAGE P ON I.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = P.PUBLICATION_ID            
END
GO
0
 

Author Comment

by:tia_kamakshi
ID: 38777673
Hi Pratima.

Thanks for your reply!!

Now INSERT and UPDATE are working fine, however DELETE TRIGGER is not getting fire and give exception while DELETING on ITEMS is done. Below is the exception detial which is generated from application from where deleting is done.

Phase: Deployment Prepare Commit Phase failed, Unable to prepare transaction: tcm:0-5607566-66560, org.hibernate.exception.SQLGrammarException: could not delete: [com.tridion.storage.ComponentMeta#component[publicationId,itemId]{publicationId=233, itemId=199071}], org.hibernate.exception.SQLGrammarException: could not delete: [com.tridion.storage.ComponentMeta#component[publicationId,itemId]{publicationId=233, itemId=199071}], Unable to prepare transaction: tcm:0-5607566-66560, org.hibernate.exception.SQLGrammarException: could not delete: [com.tridion.storage.ComponentMeta#component[publicationId,itemId]{publicationId=233, itemId=199071}], org.hibernate.exception.SQLGrammarException: could not delete: [com.tridion.storage.ComponentMeta#component[publicationId,itemId]{publicationId=233, itemId=199071}]

Open in new window


please suggest!!
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777740
try this

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'AUTN_DELETE_ITEMS' AND TYPE = 'TR')
   DROP TRIGGER AUTN_DELETE_ITEMS
GO
CREATE TRIGGER AUTN_DELETE_ITEMS ON ITEMS  
Instade of DELETE
AS
BEGIN
      INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
            SELECT
            'DEL',
            I.PUBLICATION_ID,
            I.ITEM_REFERENCE_ID,
            I.ITEM_TYPE,
            I.LAST_PUBLISHED_DATE,
            CASE WHEN I.ITEM_TYPE= 16 THEN NULL
            WHEN I.ITEM_TYPE=64 THEN P.URL END ,
            CASE WHEN I.ITEM_TYPE= 16 THEN C.SCHEMA_ID
            WHEN I.ITEM_TYPE=64 THEN NULL END            
                              FROM ITEM I
Inner join deleted INS on  I.ITEM_REFERENCE_ID = INS.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = INS.PUBLICATION_ID

            LEFT JOIN COMPONENT C ON I.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = C.PUBLICATION_ID
            LEFT JOIN PAGE P ON I.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = P.PUBLICATION_ID  

Delete from Items
where  items.ITEM_REFERENCE_ID  
in ( select ITEM_REFERENCE_ID  from deleted)
and  items.PUBLICATION_ID in ( select PUBLICATION_ID from deleted)
0
 

Author Comment

by:tia_kamakshi
ID: 38777745
Hi Pratima,

Can you please suggest same approach using the VIEW.

1) Creating a view which will hold data from tables COMPONENT, PAGE and ITEMS
2) Creating Trigger on the VIEW to populate data to AUTN_ITEMS .

Please suggest!!
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777753
I don't think that will make any change
We are doing the same thing here , you will face the same issues for delete .
still you can try that .
In that then you need to fetch the data from view instade of COMPONENT, PAGE and ITEMS
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:tia_kamakshi
ID: 38777772
Hi Pratima,

After using above modified code, application is not throwing any error, however no records in getting inserted in my AUTN_ITEMS table for unpublishing/deleting.

Please suggest!!

Would appreciate if you Can please suggest code to create view and trigger on that view.

Thanks
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777781
try this

CREATE TRIGGER AUTN_DELETE_ITEMS ON ITEMS  
FOR DELETE
AS
BEGIN
      INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
            SELECT
            'DEL',
            I.PUBLICATION_ID,
            I.ITEM_REFERENCE_ID,
            I.ITEM_TYPE,
            I.LAST_PUBLISHED_DATE,
            CASE WHEN P.ITEM_TYPE= 16 THEN NULL
            WHEN P.ITEM_TYPE=64 THEN P.URL END ,
            CASE WHEN C.ITEM_TYPE= 16 THEN C.SCHEMA_ID
            WHEN C.ITEM_TYPE=64 THEN NULL END             
            FROM DELETED I
            LEFT JOIN COMPONENT C ON I.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = C.PUBLICATION_ID
            LEFT JOIN PAGE P ON I.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = P.PUBLICATION_ID            
END
GO
0
 

Author Comment

by:tia_kamakshi
ID: 38777801
Hi Pratima,

If I use above sample code for delete my P.ITEM_TYPE and C.ITEM_TYPE are not there in COMPONENT and PAGE tables and if I change to I.ITEM_TYPE, I didn't get in URL and SCHEMA_ID entry in AUTN_ITEMS table.

Please suggest!!
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38777906
try this  after delete

CREATE TRIGGER AUTN_DELETE_ITEMS ON ITEMS  
After DELETE
AS
BEGIN
      INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
            SELECT
            'DEL',
            I.PUBLICATION_ID,
            I.ITEM_REFERENCE_ID,
            I.ITEM_TYPE,
            I.LAST_PUBLISHED_DATE,
            CASE WHEN I.ITEM_TYPE= 16 THEN NULL
            WHEN I.ITEM_TYPE=64 THEN P.URL END ,
            CASE WHEN I.ITEM_TYPE= 16 THEN C.SCHEMA_ID
            WHEN I.ITEM_TYPE=64 THEN NULL END            
            FROM DELETED I
            LEFT JOIN COMPONENT C ON I.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = C.PUBLICATION_ID
            LEFT JOIN PAGE P ON I.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = P.PUBLICATION_ID            
END
0
 

Author Comment

by:tia_kamakshi
ID: 38777977
Hi Pratima,

Using above code i.e. DEL trigger on AFTER, I am getting these type of record entries in AUTN_ITEMS and you can see blank URL and SCHEMA_ID for DEL.

ID	ACTION	PUBLICATION_ID	ITEM_REFERENCE_ID	ITEM_TYPE	LAST_PUBLISHED_DATE	URL	SCHEMA_ID	flag
3	UPD	233	342349	64	2013-01-15 16:18:56.333	/english/plan_book/ibe_unavailable.aspx	NULL	NULL
4	UPD	233	342349	64	2013-01-15 16:18:56.473	/english/plan_book/ibe_unavailable.aspx	NULL	NULL
5	UPD	233	342345	16	2013-01-15 16:18:56.600	NULL	190439	NULL
6	DEL	233	342349	64	2013-01-15 16:18:56.473	NULL	NULL	NULL
7	DEL	233	342345	16	2013-01-15 16:18:56.600	NULL	NULL	NULL

Open in new window


Please suggest!!
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38781436
I have tried same senario and working fine ...
realy not getting whats going wrong....

One possibility that I can think is now ... Records are get deleted from Component and Page table also for the same. this is the only reason why URL and SchemaID is not reflecting.

Are you deleting records from only ITEM tables or all ?
0
 

Author Comment

by:tia_kamakshi
ID: 38781693
Hi Pratima,

You are correct that records are being delete from Component and Page Table also.

So when ever unpublishing is done from application it delete all the records from ITEMS, COMPONENT and PAGE table.

Please suggest!!
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38781750
yes !!!! then this is the reason thats why URL and Schema ID are not getting updated , as records not present in Component and PAGE table.

In this case either you need to put separate Trigger now on each table or Delete records from Component and PAGE table in this trigger.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38781756
I think you need to go for this ..

while deleting , delete record only from ITem table

and apply the rigger like below

CREATE TRIGGER AUTN_DELETE_ITEMS ON ITEMS  
For DELETE
AS
BEGIN
      INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
            SELECT
            'DEL',
            I.PUBLICATION_ID,
            I.ITEM_REFERENCE_ID,
            I.ITEM_TYPE,
            I.LAST_PUBLISHED_DATE,
            CASE WHEN I.ITEM_TYPE= 16 THEN NULL
            WHEN I.ITEM_TYPE=64 THEN P.URL END ,
            CASE WHEN I.ITEM_TYPE= 16 THEN C.SCHEMA_ID
            WHEN I.ITEM_TYPE=64 THEN NULL END            
            FROM DELETED I
            LEFT JOIN COMPONENT C ON I.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = C.PUBLICATION_ID
            LEFT JOIN PAGE P ON I.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND I.PUBLICATION_ID = P.PUBLICATION_ID  


Delete from COMPONENT
where  COMPONENT.ITEM_REFERENCE_ID  
in ( select ITEM_REFERENCE_ID  from deleted)
and  COMPONENT .PUBLICATION_ID in ( select PUBLICATION_ID from deleted)        
Delete from PAGE
where  PAGE .ITEM_REFERENCE_ID  
in ( select ITEM_REFERENCE_ID  from deleted)
and  PAGE.PUBLICATION_ID in ( select PUBLICATION_ID from deleted)

END
0
 

Author Comment

by:tia_kamakshi
ID: 38781871
Hi Pratima,

Below is the outout I got using above DELETE trigger, still no values in URL and Schema_ID.

ID	ACTION	PUBLICATION_ID	ITEM_REFERENCE_ID	ITEM_TYPE	LAST_PUBLISHED_DATE	URL	SCHEMA_ID	flag
1	UPD	233	192249	64	2013-01-16 13:50:42.020	/english/plan_book/plan_and_book.aspx	NULL	NULL
2	UPD	233	192249	64	2013-01-16 13:50:42.333	/english/plan_book/plan_and_book.aspx	NULL	NULL
3	UPD	233	214529	16	2013-01-16 13:50:42.740	NULL	190489	NULL
4	UPD	233	214664	16	2013-01-16 13:50:42.770	NULL	190489	NULL
5	UPD	233	214665	16	2013-01-16 13:50:42.803	NULL	190489	NULL
6	UPD	233	199069	16	2013-01-16 13:50:42.957	NULL	190439	NULL
7	UPD	233	214666	16	2013-01-16 13:50:42.973	NULL	190489	NULL
8	DEL	233	192249	64	2013-01-16 13:50:42.333	NULL	NULL	NULL
9	DEL	233	199071	16	2013-01-15 16:32:29.177	NULL	NULL	NULL
10	DEL	233	199069	16	2013-01-16 13:50:42.957	NULL	NULL	NULL
11	DEL	233	214529	16	2013-01-16 13:50:42.740	NULL	NULL	NULL
12	DEL	233	214664	16	2013-01-16 13:50:42.770	NULL	NULL	NULL
13	DEL	233	214665	16	2013-01-16 13:50:42.803	NULL	NULL	NULL
14	DEL	233	214666	16	2013-01-16 13:50:42.973	NULL	NULL	NULL

Open in new window


"UPD" is getting application publish the page and "DEL" when application unpublish the page.

Please suggest!!
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38781882
have you stop delete the records from Component and PAGE table from your unpublishing code ?
0
 

Author Comment

by:tia_kamakshi
ID: 38781886
I can't stop the deleting of COMPONENT and PAGE as it is framework which is doing this and it is default behaviour of application.

Please suggest!!
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38782042
Will do one trick


CREATE TRIGGER AUTN_DELETE_ITEMS ON ITEMS  
For DELETE
AS
BEGIN
      INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
       Select top 1
'DEL',
            D.PUBLICATION_ID,
            D.ITEM_REFERENCE_ID,
            D.ITEM_TYPE,
            D.LAST_PUBLISHED_DATE,
            CASE WHEN D.ITEM_TYPE= 16 THEN NULL
            WHEN D.ITEM_TYPE=64 THEN A.URL END ,
            CASE WHEN D.ITEM_TYPE= 16 THEN A.SCHEMA_ID
            WHEN D.ITEM_TYPE=64 THEN NULL END

  from AUTN_ITEMS A Inner join deleted D on A.ITEM_REFERENCE_ID = D.ITEM_REFERENCE_ID AND A.PUBLICATION_ID = D.PUBLICATION_ID
where A.Action in ('ADD','UPD')
Order by A.LAST_PUBLISHED_DATE desc
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38782046
even this also need to work



CREATE TRIGGER AUTN_DELETE_ITEMS ON ITEMS  
For DELETE
AS
BEGIN
      INSERT INTO AUTN_ITEMS (ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
       Select top 1
'DEL',
            D.PUBLICATION_ID,
            D.ITEM_REFERENCE_ID,
            D.ITEM_TYPE,
            D.LAST_PUBLISHED_DATE,
             A.URL ,
           A.SCHEMA_ID
         from AUTN_ITEMS A Inner join deleted D on A.ITEM_REFERENCE_ID = D.ITEM_REFERENCE_ID AND A.PUBLICATION_ID = D.PUBLICATION_ID
where A.Action in ('ADD','UPD')
Order by A.LAST_PUBLISHED_DATE desc
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38782094
have you tired this ? working or not ?
0
 

Author Comment

by:tia_kamakshi
ID: 38782117
Hi Pratima,

After implementing above approach, I am getting below records and it seems blank entry for ADD is coming for schema_id when insert is happening, however when publish second time i get something else please see below:

INSERT
ID	ACTION	PUBLICATION_ID	ITEM_REFERENCE_ID	ITEM_TYPE	LAST_PUBLISHED_DATE	URL	SCHEMA_ID	flag
1	ADD	233	342349	64	2013-01-16 14:49:55.653	NULL	NULL	NULL
2	UPD	233	342349	64	2013-01-16 14:49:55.840	/english/plan_book/ibe_unavailable.aspx	NULL	NULL
3	ADD	233	342345	16	2013-01-16 14:49:56.060	NULL	NULL	NULL

Open in new window


UPDATE (SECOND TIME)
ID	ACTION	PUBLICATION_ID	ITEM_REFERENCE_ID	ITEM_TYPE	LAST_PUBLISHED_DATE	URL	SCHEMA_ID	flag
10	UPD	233	342349	64	2013-01-16 14:56:09.763	/english/plan_book/ibe_unavailable.aspx	NULL	NULL
11	UPD	233	342345	16	2013-01-16 14:56:09.920	NULL	190439	NULL

Open in new window


DELETE
ID	ACTION	PUBLICATION_ID	ITEM_REFERENCE_ID	ITEM_TYPE	LAST_PUBLISHED_DATE	URL	SCHEMA_ID	flag
12	DEL	233	342349	64	2013-01-16 14:56:09.763	/english/plan_book/ibe_unavailable.aspx	NULL	NULL
13	DEL	233	342345	16	2013-01-16 14:56:09.920	NULL	190439	NULL

Open in new window


What about the blank entry for "ADD" action for INSERT Trigger
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38782138
I think that is correct , because whne you first time add this there is no entry in component and page table ... so it will not get updated.

Event if you need that you need  to add trigger on component and page table to update add record.

like below




CREATE TRIGGER COMPONENT_ADD_ITEMS ON COMPONENT
For INSERT
AS
BEGIN

    Update A
Set
A.SCHEMA_ID = (
            CASE WHEN I.ITEM_TYPE= 16 THEN C.SCHEMA_ID
            WHEN I.ITEM_TYPE=64 THEN NULL END )            
From AUTN_ITEMS A Inner join COMPONENT C ON A.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND A.PUBLICATION_ID = C.PUBLICATION_ID
Inner join Inserted I ON A.ITEM_REFERENCE_ID = I.ITEM_REFERENCE_ID AND A.PUBLICATION_ID = I.PUBLICATION_ID
where A.Action = 'ADD'


CREATE TRIGGER COMPONENT_ADD_ITEMS ON PAGE
For INSERT
AS
BEGIN

    Update A
set A.URL = (CASE WHEN I.ITEM_TYPE= 16 THEN NULL
            WHEN I.ITEM_TYPE=64 THEN P.URL END)
           
From AUTN_ITEMS A Inner join PAGE P ON A.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND A.PUBLICATION_ID = P.PUBLICATION_ID
Inner join Inserted I ON A.ITEM_REFERENCE_ID = I.ITEM_REFERENCE_ID AND A.PUBLICATION_ID = I.PUBLICATION_ID
where A.Action = 'ADD'
0
 

Author Comment

by:tia_kamakshi
ID: 38786347
Hi Pratima,

Thanks for reply!!

In your above code, I am getting error on I.ITEM_TYPE

Msg 207, Level 16, State 1, Procedure COMPONENT_ADD_ITEMS, Line 7
Invalid column name 'ITEM_TYPE'.

As in INSERTED of component we don't have ITEM_TYPE, so trigger creation is giving error.

Can you please check it again for below code, I make it bold where error is coming

CREATE TRIGGER COMPONENT_ADD_ITEMS ON PAGE
For INSERT
AS
BEGIN

    Update A
set A.URL = (CASE WHEN I.ITEM_TYPE= 16 THEN NULL
            WHEN I.ITEM_TYPE=64 THEN P.URL END)
           
From AUTN_ITEMS A Inner join PAGE P ON A.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND A.PUBLICATION_ID = P.PUBLICATION_ID
Inner join Inserted I ON A.ITEM_REFERENCE_ID = I.ITEM_REFERENCE_ID AND A.PUBLICATION_ID = I.PUBLICATION_ID
where A.Action = 'ADD'
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 38786840
No need to check Item_type
try this

CREATE TRIGGER PAGE_ADD_ITEMS ON PAGE
For INSERT
AS
BEGIN

    Update A
set A.URL = P.URL            
From AUTN_ITEMS A Inner join PAGE P ON A.ITEM_REFERENCE_ID = P.ITEM_REFERENCE_ID AND A.PUBLICATION_ID = P.PUBLICATION_ID
Inner join Inserted I ON A.ITEM_REFERENCE_ID = I.ITEM_REFERENCE_ID AND A.PUBLICATION_ID = I.PUBLICATION_ID
where A.Action = 'ADD'

CREATE TRIGGER COMPONENT_ADD_ITEMS ON COMPONENT
For INSERT
AS
BEGIN

    Update A
Set
A.SCHEMA_ID =C.SCHEMA_ID
       
From AUTN_ITEMS A Inner join COMPONENT C ON A.ITEM_REFERENCE_ID = C.ITEM_REFERENCE_ID AND A.PUBLICATION_ID = C.PUBLICATION_ID
Inner join Inserted I ON A.ITEM_REFERENCE_ID = I.ITEM_REFERENCE_ID AND A.PUBLICATION_ID = I.PUBLICATION_ID
where A.Action = 'ADD'
0
 

Author Closing Comment

by:tia_kamakshi
ID: 38786860
Excellent solution thanks Expert!!
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38786876
Your welcome :)

Glad to see that it get solved
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now