-1

I am trying to create a trigger for a new table (X) which pulls data from an existing view(Y). (Table Y being a replica of view X)

I am aware of creating trigger to pull data from a view to its own base table.

As of now I inserted data from Y to X using insert into statement. I need to set a trigger on Y, such that an update/insert on Y is reflected on X as well.

This is what I am trying to do:

create trigger TableUpdate on X

instead of insert, update as

Begin

insert into Y

select * from inserted

end

This looks like it updates only its base tables and not an external table.

Angela
  • 43
  • 6
  • This question is so vague. What do you mean it only updates base tables not an external table? Your instead of trigger will insert data into X and will not insert anything into Y (which seems kind of silly). I think perhaps what you really want here is to look at replication instead of doing this with triggers. – Sean Lange Apr 23 '19 at 16:34
  • The "best" way is simply not to replicate data - period. This smells like an [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). What are you trying to accomplish here? Are you attempting to take some sort of snapshot so that you can do some comparison over time? – SMor Apr 23 '19 at 16:56
  • Since the view takes a while to execute, I am trying to pull this data to a table for faster execution. – Angela Apr 23 '19 at 16:58
  • What do you mean the data flow is from `X` to `Y`? If `Y` is a `VIEW` it doesn't store any data; a `VIEW` is simply a precompiled `SELECT` statement. If you `INSERT` into `Y`, and then the data will actually be inserted into `X`, not `Y` (but you'll see the data in both places). [DB<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3d5fdd1c2d6b1ba03c843d34150fc3fe) – Thom A Apr 23 '19 at 17:33
  • Y is a view created from a different table (say A). I am trying to replicate Y's data into a new table X. Would that be possible? – Angela Apr 23 '19 at 17:39
  • 1
    Why can't you just write the reverse of the trigger you have? Put a trigger on Y that inserts to X. It's unclear what it is you're actually asking, because you seem to already know how to do what you want to do. What do "base tables" and "external tables" even mean to you? – Tab Alleman Apr 23 '19 at 17:44
  • 1
    Now that it is clear that the **real** problem is that the view is slow you should focus on fixing the view so it isn't slow. Why is your view slow? – Sean Lange Apr 23 '19 at 21:00

1 Answers1

0

You cannot detect data changes in view using triggers when an underlying (base) table data changes. You need to add triggers to tables (say Table A) that form this view to catch data changes.

To summarise:

  • Assuming View Y takes data from Table A.
  • Assuming View Y has INSTEAD OF trigger.
  • When data is changed in Table A, trigger in View Y will NOT fire. It will only fire when you directly insert/update View Y.

Conclusion

As @SeanLange said you need to look at optimising your view and/or finding ways to not use it.

I suggest you take a look at Materialised Views as possible way to optimise View performance.

Alex
  • 4,885
  • 3
  • 19
  • 39