Create a database for a wiki (site that allows collaborative modification of its

Question

Create a database for a wiki (site that allows collaborative modification of its content and structure directly from the web browser). We needs to keep a history of the modifications of each page by keeping each version of each page as a separate row: PageRevision(name, date, author, text)
a) Create a view called Page(name, last_author, text). This view should shows only the latest version of each page.
b) Create a trigger called EditPage on your newly created view Page. When a user tries to update (this should be understood as an insertion or update) a given page, a new revision is created instead (new line into the PageRevision Table).
c) Explain what the next trigger is performing:
CREATE TRIGGER DeletePage INSTEAD OF DELETE ON Pages
FOR EACH ROW
DELETE FROM PageRevisions WHERE name = OLD.name;
INSERT INTO deletelog VALUES (old.name, NOW());

Explanation / Answer

1. CREATE VIEW Page

AS SELECT top 1 name, Last_Author, text

FROM PageRevision order by date desc;

2. CREATE TRIGGER EditPage ON [dbo].[PageRevision]

FOR INSERT

AS

declare @name varchar(100);

declare @date date;

declare @author varchar(100);

declare @text nvarchar();

insert into PageRevision

(name,date,author,text)

values(@name,getdate(),@author,@text);

GO

These can be used as an interceptor for anything that anyone tried to do on our table or view.

If you define an Instead Of trigger on a table for the Delete operation,

they try to delete rows, and they will not actually get deleted (unless you issue another delete instruction from within the trigger).

This trigger will loop through every row in Pages table and compares the Name column with Name column in PageRevisions table. If matching records are found

those will be deleted.

The deleted records information is logged in deletelog table which contains name and the time when they are delete.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.