ms sql triggers

Dear Experts,

I have a two tables in a Parent/child relationship and another one to store some values from both.(lets call it table C)

What I want to achieve is when I insert a record in the parent table, a row is inserted in table C.When the parent record is updated,the row in C is also updated.
And when the first child row is created, the same row is updated with the child details.
When another child row is created,the first row in C is duplicated with details of the Parent table and the values of the new child rows.

I also want when the parent row is deleted ,all child rows are deleted(accomplished already using cascading referencial integrity) - but need the rows in table C to remove the deleted child and parent data.

Thanks In Advance
LVL 12
Norman MainaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AhmedHindyCommented:
hi NormanMaina,

i supposed that I have 3 table with that structure :

ParentTable:
     Have a Trigger

 
CREATE TABLE [dbo].[Parents](
	[ParentID] [int] IDENTITY(1,1) NOT NULL,
	[ParentName] [varchar](50) NULL,
 CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED 
(
	[ParentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[ParentTriggers]
   ON  [dbo].[Parents] 
   AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @ParentID INT
	SELECT @ParentID = ParentID FROM INSERTED
	
	IF @ParentID IS NULL
		BEGIN
			SELECT @ParentID = ParentID FROM DELETED
			DELETE FROM [BackUp] WHERE ParentID = @ParentID
		END
	ELSE
		BEGIN
			IF EXISTS(select ParentID FROM [Backup] WHERE ParentID = @ParentID )
				BEGIN
					DECLARE @ParentName VARCHAR(50)
					SELECT @ParentName = ParentName FROM INSERTED
					
					UPDATE [BackUp] SET ParentID = @ParentID, ParentName = @ParentName where ParentID = @ParentID
				END
			ELSE
				BEGIN
					INSERT INTO [BackUp] (ParentID, ParentName) SELECT ParentID, ParentName FROM INSERTED
				END
		END
END
GO

Open in new window


ChildTable :
     Have a Trigger

 
CREATE TABLE [dbo].[Childs](
	[ChildID] [int] IDENTITY(1,1) NOT NULL,
	[ChildName] [varchar](50) NULL,
	[FKParentID] [int] NOT NULL,
 CONSTRAINT [PK_Childs] PRIMARY KEY CLUSTERED 
(
	[ChildID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TRIGGER [dbo].[ChildTriggers] 
   ON  [dbo].[Childs] 
   AFTER INSERT,UPDATE, DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- After Insert
    
    DECLARE @ChildID INT
    SELECT @ChildID = ChildID FROM INSERTED
    
    IF @ChildID IS NULL 
		BEGIN
			SELECT @ChildID = ChildID FROM DELETED
			DELETE FROM [BackUp] WHERE ChildID = @ChildID
		END
	ELSE
		BEGIN
			DECLARE @ChildName VARCHAR(50)
			SELECT @ChildName = ChildName FROM INSERTED

			DECLARE @ParentID INT
			SELECT @ParentID = FKParentID FROM INSERTED
	
			DECLARE @Count INT
			SELECT @Count = COUNT(*) FROM Childs WHERE FKParentID = @ParentID
			print @count
	
			IF (@Count > 1)
				BEGIN			
					IF EXISTS(SELECT ChildID FROM [BackUp] WHERE ParentID = @ParentID AND ChildID = @ChildID)
						BEGIN							
							UPDATE [BackUp] SET ChildName = @ChildName WHERE ParentID = @ParentID AND ChildID = @ChildID
						END
					ELSE
						BEGIN
							INSERT INTO [BackUp] (ParentID, ParentName, ChildID, ChildName) 
								SELECT ParentID, P.ParentName, ChildID, ChildName FROM INSERTED INNER JOIN Parents P ON P.ParentID = INSERTED.FKParentID
						END
				END
			ELSE
				BEGIN			
					UPDATE [BackUp] SET ChildID = @ChildID, ChildName = @ChildName WHERE ParentID = @ParentID
				END
		END
END
GO

Open in new window


BackUpTable :

 
CREATE TABLE [dbo].[Backup](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ParentID] [int] NOT NULL,
	[ParentName] [varchar](50) NULL,
	[ChildID] [int] NULL,
	[ChildName] [varchar](50) NULL,
 CONSTRAINT [PK_Backup] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Open in new window


try to insert, update and delete into parent, child tables and take a look at the backup table for the updates

Note:
I'm leaving you to delete the childs when you delete a parent as you said using cascading referencial integrity
0
AielloJCommented:
NormanMaina:

Could you provide some detail about why you need to do this?  Experience has shown that trying to code such a solution usually becomes a maintenance / debugging project forever.  You might want to look into database replication which is a SQL-Server built in methodology for doing exactly what you're trying to do.  It has some limitations, but it's a canned solution that may do exactly what you need.

Regards,

AielloJ
0
Scott PletcherSenior DBACommented:
The Parent_Table triggers are easy, those are below.

The Child_Table triggers are slightly move involved, because the *first* insert to the child table just requires an UPDATE on table_c, but the other child_table inserts require a table_c INSERT.  And the first and additional INSERTs could be on the same statement.
CREATE TRIGGER Parent_Table_Trigger_Ins
ON Parent_Table
AFTER INSERT
AS
INSERT INTO Table_C (parent_key, datacol1, datacol2) --, ...)
SELECT parent_key, datacol1, datacol2 --, ...
FROM inserted

GO

CREATE TRIGGER Parent_Table_Trigger_Upd
ON Parent_Table
AFTER UPDATE
AS
UPDATE c
SET
    c.datacol1 = i.datacol1,
    c.datacol2 = i.datacol2 --, ...
FROM Table_C c
INNER JOIN inserted i ON
    i.parent_key = c.parent_key

GO

CREATE TRIGGER Parent_Table_Trigger_Upd
ON Parent_Table
AFTER DELETE
AS
DELETE FROM c
FROM Table_C c
INNER JOIN inserted i ON
    i.parent_key = c.parent_key
    
GO

Open in new window

0
Scott PletcherSenior DBACommented:
Below is the Child_Table INSERT trigger.  The UPDATE and DELETE triggers are much more straightforward and similar in style to the Parent triggers above.  If you need help with those triggers too, pls let me know.

Btw, it is not safe to use a variable to read from the inserted (/deleted) tables in SQL triggers without using a loop = cursor.  In SQL Server, a trigger will fire only once per statement, no matter how many rows that statement INSERTed / UPDATEd / DELETEd.
CREATE TRIGGER Child_Table_Trigger_Ins
ON Child_Table
AFTER INSERT
AS
DECLARE @update_was_needed bit

UPDATE c
SET
    c.child_key = i.child_key,
    c.child_data1 = i.datacol1,
    c.child_data2 = i.datacol2 --, ...
FROM Table_C c
CROSS JOIN (
    SELECT TOP 1 *
    FROM inserted
    WHERE i.child_key = (SELECT MIN(child_key) FROM inserted)
) AS i
WHERE
    c.child_key IS NULL
    
IF @@ROWCOUNT > 0
    SET @update_was_needed = 1
ELSE
    SET @update_was_needed = 0
    
INSERT INTO Child_Table (col_name) --, ...
SELECT col_name --, ...
FROM inserted i
WHERE
    (@update_was_needed = 0 OR i.child_key <> (SELECT MIN(child_key) FROM inserted))

GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Norman MainaAuthor Commented:
AhmedHindy>sorry for teh late response,(was caught up in another project) -i will try your suggestion shortly and getback to you

AielloJ>Am very aware of replication-and i do not think it the purpose.

This is what I want to achieve -and I believe triggers would be ideal here- I welcome ideas of better ways of getting same results.

I have an Invoice table -with Invoice date,Invoice Noquantities and value fields  --(NOTE:invoicing is done for provisional orders because of shipping logistics)
And a Sales table with - Invoice no,Sale Date,quantity and value fields.(to store reciepts of money against an invoice)

The invoice amount can be paid for over a couple of days - and the invoice can also be partially sold i.e the customer may reduce order from provitional so the quantity remaining can be sold to another customer(auction etc).

I wanted to have another table to track invoices vs sales -and thought triggers would be perfect to use.Am still sure its what I need, or the other way would be to use SP's for the calculations on the indvidual tables but am not sure how this will work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.