• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

Trigger SQL server 2008

I have two table
TableA

ID   Date                 QAY/N
1    01/01/23                0

TableB

I would like to create a trigger if table QA/Y/N column change to 1
Then insert the row to Table B and delete the row from A

Look like beow.. How can I create a trigger to do this using SQL 2008?

TableA


TableB
ID   Date                 QAY/N    Ready
1    01/01/23                1
0
jung1975
Asked:
jung1975
2 Solutions
 
JestersGrindCommented:
This should work.  I'm assuming ID is the primary key.

Greg


CREATE TRIGGER TR_TableA_Update ON TableA
AFTER UPDATE
AS
BEGIN
IF EXISTS(SELECT * FROM TableA a INNER JOIN Inserted b ON a.ID = b.ID WHERE b.[QAY/N] = 1)

	BEGIN

	INSERT INTO TableB 
	SELECT Inserted.ID, Inserted.[Date], Inserted.[QAY/N]
	FROM Inserted
	
	DELETE FROM TableA
	FROM TableA a INNER JOIN Inserted b ON a.ID = b.ID

	END

END

Open in new window

0
 
HainKurtSr. System AnalystCommented:
dont write trigger, write sp

in this sp, check existing record & new record
it it meets conditions, delete from one table and insert into other one...

with trigger this is really a weird situation... I recommend dont write any trigger, any sp nothing...

drop tableB
create two views

create v_A as select * from tablea where [qay/n]=0
create v_B as select * from tablea where [qay/n]=1

insert into tableA as usual, but use views when required to get the data...


0
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now