?
Solved

Trigger SQL server 2008

Posted on 2011-10-11
2
Medium Priority
?
167 Views
Last Modified: 2012-05-12
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
Comment
Question by:jung1975
2 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 1000 total points
ID: 36951180
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
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 1000 total points
ID: 36951191
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

864 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