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

SQL except with trigger

I have an update trigger, when it fires all the data from the updated row is inserted into another table.

I would like it to only insert the changed data into the the other table. I see how "except" can be used to do this but I need all the changes to be inserted as well as the the ID column (it cant change and will not show up in an 'except' but without it I have no meaningful way to use the data).

Is there a way to use "except" but also grab the ID column regardless of any changes?

0
RustyZ32
Asked:
RustyZ32
  • 5
  • 3
  • 2
  • +1
1 Solution
 
mmr159Commented:
Inside your update trigger code:

DECLARE @id INT

SELECT @id=ID FROM inserted

?
0
 
RustyZ32Author Commented:
here is what I have to insert only the changed values:

insert into [othertable]
select * from inserted

except

select * from deleted



the problem is this does not include the "ID" value because that column never changes. I need that value to always be included despite any changes.
0
 
mmr159Commented:
DECLARE @id INT

SELECT @id=ID FROM inserted

insert into [othertable] (list,all,columns,including,id)
select @id,list,all,columns,except,id from inserted

except

select @id,list,all,columns,except,id from deleted

Of course, make sure the column order is correct....
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
TempDBACommented:
mmr159,
    I am afraid with your approach as if there is a batch update (updating value more than 1 row), this will just insert a single value.

RustyZ32,
     While inserting the value in the loggin table where you are inserting the updated row through trigger, you can check if the value already exists. Here is what inserted and deleted tables hold, that you get from BOL.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.
(Ref:- http://msdn.microsoft.com/en-us/library/ms191300.aspx)

So, while logging it thorough trigger, you can make use of where not exists. Suppose you have trigger on Table A and Table B is where you are logging changes through the trigger, you can do something like:-

insert into TableB(id,bla,bla,bla)
select * from inserted  i   where not exists (select 1 from TableB t1 where t1.id = i.id)

But this should insure you that  you have proper index on id column and there is no repetable updates on the TableA.
0
 
RustyZ32Author Commented:
mmr159:  the syntax works but it still inserts all values into the logging table, regardless of change.

tempdba:  what exactly do you mean by a repetable update?

0
 
TempDBACommented:
by repeatable update, I mean to say that suppose I did some change in TableA today. The data will be inserted into TableB as it dosn't exists.
Tomorrow or some day later, again I changed the value in TableA, the value won't be updated in the TableB(logging table) as the id is already present in TableB.
0
 
RustyZ32Author Commented:
tempdba: I tried your idea but it doesnt insert anything at all, though the update occurs so the trigger must be completing to its satisfaction.

0
 
RustyZ32Author Commented:
Ok, that explains why it wasn't inserting (i was using the same ID for testing). I truncated the logging table to start fresh, it inserts now but like mmr159's solution it is still inserting all values, changed or not.

0
 
RustyZ32Author Commented:
I did some manual testing of the 'except'

if i do an except as follows:

select (columns) from tableA
where ID IN (select ID from tableB)
except
select (columns from tableB)

all records show, as i nothing is hte same (even though all but one value is identical).

if I do the same statement but only on TableA (excepting itself) it works as it should.

the colunm names and datatypes are identical in each table.
0
 
TempDBACommented:
You can do one more thing, though it gives some duplicates in terms of ids.
As and when you insert data into table A , insert the data into table B along with an extra column called modified date where the default value is current datetime. This will give you a hold as you can track the changes with ids followed by what values changed at what time.
0
 
LowfatspreadCommented:

insert into [othertable]
select * from inserted

except

select * from deleted


ok the above will insert any row from the inserted table which isn't exactly the same in the deleted table...
into the othertable provided no key constraints are invalidated....

what is the primary key/unique keys defined on othertable....?

please take a step back and explain exactly what you are trying to achieve....
please include some example data
also consider what you want to happen if data for the id already exists in othertable...

please  tell us what id is ... how it is maintained ... what a change actually means (and what exactly you are trying to capture ... in data terms e.g. are differing spaces significant in character based columns?


0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now