Start Free Trial

asked on

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

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!!

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

ASKER

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!!

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

ASKER

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.

Can you please provide me some data from this tables so it is easy to unnderstand what's going wrong ?

ASKER

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

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

ASKER

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

you are getting problem with only delete one right ?

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)

ASKER

Hi Pratima,

INSERT and DELETE both,

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

Please suggest!!

ASKER

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

share the output of query to me in last comment I have given

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

ASKER

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

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

ASKER

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!!

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)

ASKER

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!!

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

ASKER

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

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

ASKER

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!!

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

ASKER

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!!

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 ?

ASKER

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!!

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.

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

ASKER

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!!

have you stop delete the records from Component and PAGE table from your unpublishing code ?

ASKER

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!!

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

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

have you tired this ? working or not ?

ASKER

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

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'

ASKER

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'

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Excellent solution thanks Expert!!

Your welcome :)

Glad to see that it get solved