Trigger on View

Question

Post reply

Trigger on View

adisql

SSCrazy

Points: 2893
More actions
August 24, 2020 at 9:24 pm

#3783071

Hi ,
Trying to create Trigger on View to insert the records in other table like below.
But it is not working . Any suggestions please .
USE [TESTDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE [dbo].[Badgeid]( [cardnumber] [int] NULL) ON [PRIMARY]
GO
create VIEW [dbo].[View_BadgeID]
AS
SELECT [cardnumber]
FROM [dbo].[BadgeID]
GO
CREATE TABLE [dbo].[BadgeId2]([cardnumber] [int] NULL) ON [PRIMARY]
GO
create Trigger Trig_BadgeID1
on [dbo].[View_BadgeID]
instead of insert
as
begin
insert into [dbo].[Badgeid2]
select [cardnumber] from inserted
end;

--cardnumbers are not inserting in BadgeId2 table when cardnumber inserted in BadgeId table.
INSERT INTO [dbo].[Badgeid]([cardnumber]) VALUES (43225)
select * from Badgeid2
Result:(0 row(s) affected)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1335 More actions · Answer 1

Assuming you're doing this in SSMS, you need a GO statement after your trigger definition. Otherwise, your insert and select are just part of the trigger definition, and are not executed until you do another insert.
You need to insert into the view on which the trigger is defined, not into the underlying table. Otherwise, the trigger doesn't fire.

adisql SSCrazy Points: 2893 More actions · Answer 2

Thank you Rick!!

Yes , It is working.

But my actual goal is , if the record insert into the underlying table then same record will be insert into the other table.

But the trigger should be on view. is it possible ?

ratbak Ten Centuries Points: 1335 More actions · Answer 3

The trigger (and insert) would be on the view if you're trying to prevent insert into dbo.Badgeid and instead insert into Badgeid2.

Are you saying you also still want data inserted into dbo.Badgeid? If so, then an INSTEAD trigger is not appropriate approach. If you want to insert into dbo.Badgeid and dbo.Badgeid2, you could discard the view, change the trigger to a FOR/AFTER trigger on dbo.Badgeid, and insert into dbo.Badgeid directly (with the trigger also inserting into Badid2). But adding

insert into [dbo].[Badgeid]

select [cardnumber] from inserted;

to the INSTEAD trigger would also work.

adisql SSCrazy Points: 2893 More actions · Answer 4

Thank you Rick.I totally understand your explanation.

We have some agreement restrictions for not to create trigger on Table.

So I am trying to create trigger on view.

When ever cardnumber insert into Badgeid table then same cardnumber should be insert into Badgeid2 table.

Thank you.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 5

adisql wrote:

But my actual goal is , if the record insert into the underlying table then same record will be insert into the other table.
But the trigger should be on view. is it possible ?

Yes... it's possible. But, I have to ask, why are you duplicating data? What is the business reason for doing so here?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9295 More actions · Answer 6

>> Trying to CREATETrigger on View to insert the records [sic] in other table like below. <<

Your whole approach this problem is wrong. By definition, not by option, a table must have a key. So the first thing we have to do is give a proper name to your first table and get it a A key. Here is my guess, since you gave us nothing to work with. Also, since a card number is an identifier, it must be on a nominal scale and nominal scales are never numeric by definition.

CREATE TABLE Badges

(card_nbr CHAR(5) NOT NULL PRIMARY KEY,

card_status CHAR(2) NOT NULL

CHECK (card_status IN (...));

Next, your view is again wrong. If you'd really like to get cussed out for coding like this, then read some of Chris Date's books and articles. The first mistakes are that "View_BadgeID" is prefixed with metadata (this is the RDBMS equivalent of putting "noun_" in front of every noun when you write an essay). A view is just as much a table as a base table and we have not needed to put syntax cues in front of data element names since FORTRAN. We can clearly see that "Badge_id" is singular, while a table is a set. Sets are either collective nouns or plurals, by their very nature. But finally the definition of this view is redundant. You have two tables that model the same data! Chris Date would hang you for this. Likewise, your "BadgeId2" table is not a table - no key), improperly named and redundant.

The whole reason we went to databases from the file system that you're imitating an SQL was to remove redundancy. This is why we normalize schemas.

Next, SQL is supposed to be a declarative language, which means we don't use procedural code whenever possible. In theory, there is actually a formal proof that says you never have to use procedural code. Look up primitive recursive functions and their equivalency to stack machines. This is a lot of theory, but let me tell you as someone who voted on this stuff was on the standards committee, the only reason we have triggers is that we didn't know about declarative programming and the first SQL systems were built on top of existing procedural filesystems.

Instead of physically moving data from one table to another, an SQL programmer would have a column that indicates why the occurrence of this data into one table is totally different logically from the same data in a second table. What you're doing is a deck of punch cards that you slide around on the tabletop as it were 1950.

This is why I added card status to your non-table. You can now do views or select statements based on their status.

Please post DDL and follow ANSI/ISO standards when asking for help.

adisql SSCrazy Points: 2893 More actions · Answer 7

We have to transfer the data to other system in real time.

The other table resides in oracle database.

So first I am testing with sql table if it works then I will replace it with oracle table using linked server.

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 8

adisql wrote:

We have to transfer the data to other system in real time.
The other table resides in oracle database.
So first I am testing with sql table if it works then I will replace it with oracle table using linked server.

Since you are looking at using a linked server - I would NOT recommending putting that into a trigger. In a trigger - if the linked server is unavailable for any reason then the transactions will fail and rollback.

It might be a better option for you to look into service brokers. With a service broker - you monitor the identified table for changes and queue the data in a service queue. A separate process is then utilized to process the queue and send the data to the Oracle system. It is a much more complex solution but would also be much safer - as the queued items could still be processed after the linked server is back up and available.

Just an idea...

It would be much easier if you were able to support a lag in the data transfer. Even a lag of a few minutes would allow for an agent job to be scheduled to identify new rows added/updated and sent to Oracle.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs