February 28, 2017 at 9:03 am
I have created a view which as you know pulls information from base tables.
Now this view stores live information pertaining to customers orders. However once a customer closes their account the live base tables update as does the view.
I wanted to create a trigger that deletes entries in the view once the account is closed.
Upon running the code below I realized that you can't create such triggers on views.
I stumbled upon "INSTEAD OF" triggers however I am concerned that this may delete records from my base tables that make up the view which I can't afford to happen.
Any ideas how I can go about executing this?
thanks in advance!
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [DBO].[TR_CUSTOMERVC_CLOSED] ON [Work].[dbo].[vw_Customer_Log]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM [Work].[dbo].[vw_Customer_Log]
WHERE EXISTS ( SELECT*
FROM [Work].[dbo].[vw_Customer_Log]
WHERE status='C');
END
February 28, 2017 at 9:08 am
Why not just change the view definition so as not to display any rows for inactive customers?
John
February 28, 2017 at 9:19 am
Yeah I realise that is the easier option( and most likely what I will do), however I was merely enquiring if there is a way around this.
thanks for the reply and apologies for wasting anyones time
February 28, 2017 at 9:23 am
You don't "delete" from views. Views are simply a predefined SELECT statement. If you need something deleted that is displayed in a view, you have to delete it from the base table. If your view collects data from several tables, that may mean deleting data in all or some of those tables.
As John said, I don't think the aim should be to "delete" the data in the table. it should be to filter that data in the view. That might be as easy as adding the following to your WHERE statement on your view:AND status != 'C'
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 28, 2017 at 9:58 am
Thom A - Tuesday, February 28, 2017 9:23 AMYou don't "delete" from views. Views are simply a predefined SELECT statement. If you need something deleted that is displayed in a view, you have to delete it from the base table. If your view collects data from several tables, that may mean deleting data in all or some of those tables.As John said, I don't think the aim should be to "delete" the data in the table. it should be to filter that data in the view. That might be as easy as adding the following to your WHERE statement on your view:
AND status != 'C'
Or, if you are bothered about ANSI compliance
AND status <> 'C'
😉
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 1, 2017 at 2:08 am
Thanks for all the replies I realized that its much easier to edit the select statement that made the view.
Now I have an extension to this problem;
I have a separate view that houses all the customer details that have closed their account.
Is there a way (I'm thinking stored procedure) to alert me every time a new entry is made into this view?
Secondly using an "instead of" trigger would I be right in assuming that I will be able to insert records into a separate table?
Im very dubious about using "instead of" triggers on views as I don't want to change the information that's displayed in the view.
Thanks in advance
March 1, 2017 at 2:24 am
If you must use triggers, put them on the base table(s) instead of on the view. But consider first whether there's an alternative solution - maybe a job that polls the table every five minutes? After all, do you really need to be alerted immediately, every time the table changes? And yes, you can use a trigger to insert into a table. Most of the information you've asked for is in the CREATE TRIGGER topic in Books Online, incidentally.
John
March 1, 2017 at 2:28 am
You can set up something yes, but how depends. If, for example, you use a Stored procedure, you'd need to look for records that have been added. How do you know if a record has been added to your table? You could also use a trigger on INSERT on the table, however, I dislike this approach (as it may require users inserting row to have additional permissions).
You could redirect rows from an INSERT to a different table, but WHY? I think you're understanding of views may be flawed. If the view is defined to only display Active Customers, for example, then you will only see active customers in that view. If one of the customers is set to inactive, it will no longer be displayed in the View.
Take a simple Example like below:USE DevTestDB;
GO
CREATE TABLE Customer (CustomerID INT IDENTITY(1,1),
CustomerName VARCHAR(10) NOT NULL,
IsActive BIT DEFAULT 1);
GO
INSERT INTO Customer (CustomerName)
VALUES ('Smith'),('Bloggs'),('Green'),('Brown'),('Obama');
GO
SELECT *
FROM Customer;
GO
CReATE VIEW ActiveCustomer AS
SELECT CustomerID,
CustomerName
FROM Customer
WHERE IsActive = 1;
GO
--This will return all customers As they are all Active
SELECT *
FROM ActiveCustomer;
GO
--Let mark a couple Inactive
UPDATE Customer
SET IsActive = 0
WHERE CustomerID In (2,4);
GO
--Do the SELECTs again, notice the difference?
SELECT *
FROM Customer;
SELECT *
FROM ActiveCustomer;
GO
--Clean up
DROP VIEW ActiveCustomer;
DROP TABLE Customer;
GO
Notice I didn't "DELETE" from the view. I simply updated the table to set two customers to be no longer Active. Those customers then no longer showed in the view, as they are filtered out in the VIEWs WHERE clause.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 1, 2017 at 2:34 am
Both very comprehensive replies!
thanks again guys
Much appreciated
March 2, 2017 at 9:51 am
ExhibitA - Wednesday, March 1, 2017 2:08 AMThanks for all the replies I realized that its much easier to edit the select statement that made the view.
>> I have a separate view that houses all the customer details that have closed their account. <<
Your mindset is wrong. Views do not house anything; they are invoked when their name is used in a statement. They are not scratch tapes!
>> Is there a way (I'm thinking stored procedure) to alert me every time a new entry is made into this view? <<
Again, your mindset's completely wrong. Nothing is made into the view; the view is invoked and constructed every time.
>> Secondly using an "instead of" trigger would I be right in assuming that I will be able to insert records [sic] into a separate table? <<
Yes, but this the terribly bad idea. The reason we put instead of triggers into the standard was because of the view update ability problem. There are proofs in theoretical computer science that there is no general way to update a view. So this was our way of letting you escape to procedural code (a trigger, which generally we hate) and do whatever rule you wanted to write for your particular situation.
You also have another design error. A status is a state of being; this means it has a temporal dimension, (starting timestamp, ending timestamp) for which it is valid. It's not a flag the way your writing it. It is also a "<something in particlar>_status" and not a vague generic "status", which is how you might've shown it in a COBOL file. When it's temporal dimension is available, you can then look at the status and the time frame in your views. Each time you invoke the view the correct time frame and status code will be used.
Please post DDL and follow ANSI/ISO standards when asking for help.
March 6, 2017 at 8:02 am
Before I begin let me apologise for my earlier posts. It seems that I may offended some of the more experienced SQL users. I am now fully aware of how a view works and its limitations.
With this said please can anyone assists with my problem:
So I have a view that is composed of customer information :
[Account.Number]
[D.O.B]
[LastPaymentDate]
[CurrentBalance]
[Status]
Now this view updates every time a customer's account is closed (status= closed)
Now my previous post(see above) has been solved, and the trigger questions stem from a need to insert details (from the view) into a mailing table that runs automatically and mails customers.
Right, in an attempt to solve this dilemma, I created a table from my view ( Select* into #newtable from oldtable) so that I can set up a trigger on this table(NEW TABLE) to then input details into the mailing table (to send a closed account letter etc...).
I have written a Stored procedure that updates my table from my view each time the view changes.
However, each time I run the stored procedure it truncates the values from my table and only inserts the last entry from my view. Any ideas?
Apologies if this is confusing.
USE [WORK]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [DBO].[USP_CUSTOMER_VC_CLOSED]
AS
DECLARE @ACCOUNT_NUMBER CHAR(19)
DECLARE @CURRENTBALANCE DECIMAL (20,3)
DECLARE @DATEIDENTIFIED DATE
DECLARE @LASTPAYMENTDATE DATE
DECLARE @status
SELECT @ACCOUNT_NUMBER=(ACCOUNT_NUMBER) FROM [Work].DBO.vw_CUST_VC_CLOSED
SELECT @CURRENTBALANCE=(CURRENTBALANCE) FROM [Work].DBO.vw_CUST_VC_CLOSED
SELECT @DATEIDENTIFIED=(DATEIDENTIFIED) FROM [Work].DBO.vw_CUST_VC_CLOSED
SELECT @LASTPAYMENTDATE=(LASTPAYMENTDATE) FROM [Work].DBO.vw_CUST_VC_CLOSED
SELECT @status=(@NOCOMMITMENT) FROM [Work].DBO.vw_CUST_VC_CLOSED
BEGIN
SET NOCOUNT ON
IF (SELECT DATENAME(DW,GETDATE()))='MONDAY'
INSERT INTO [WORK].[dbo].[T_CUSTOMER_VC_CLOSED]
(
account_number
,currentbalance
,dateidentified
,lastpaymentdate
,status
)
values
(
@ACCOUNT_NUMBER
,@CURRENTBALANCE
,@DATEIDENTIFIED
,@LASTPAYMENTDATE
,@STATUS
)
END
March 6, 2017 at 8:14 am
Yes, it's very confusing! How does that stored procedure get invoked? It won't just run by magic when something changes, And don't forget - it's not the view that changes, it's the underlying tables. Your code is attempting to assign multiple values to scalar variables - you didn't mention getting an error message, but you must do, surely?
John
March 6, 2017 at 8:21 am
John Mitchell-245523 - Monday, March 6, 2017 8:14 AMYes, it's very confusing! How does that stored procedure get invoked? It won't just run by magic when something changes, And don't forget - it's not the view that changes, it's the underlying tables. Your code is attempting to assign multiple values to scalar variables - you didn't mention getting an error message, but you must do, surely?John
The stored procedure would ideally run every Monday to update the table.
I understand it's the underlying table in the view's that change, which in turn causes the view to change.
All I want to do is insert everything from my view into my table. Am I asking the wrong questions??
It's difficult to elaborate fully on a forum about sensitive work related stuff.
I don't get an error message when I run the SP
March 6, 2017 at 9:30 am
ExhibitA - Monday, March 6, 2017 8:21 AMThe stored procedure would ideally run every Monday to update the table.I understand it's the underlying table in the view's that change, which in turn causes the view to change.
All I want to do is insert everything from my view into my table. Am I asking the wrong questions??
It's difficult to elaborate fully on a forum about sensitive work related stuff.
I don't get an error message when I run the SP
The view does not change. The data that the view returns is what changes.
This seems like an unusual way to satisfy your requirement. What an account is closed, is there a date column which is populated to indicate the date on which it is closed?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 6, 2017 at 9:36 am
ExhibitA - Monday, March 6, 2017 8:21 AMAll I want to do is insert everything from my view into my table.
INSERT INTO MyTable (
Col1
, Col2
...
, Coln
)
SELECT
Col1
, Col2
...
, Coln
FROM MyView
John
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply